Filtering and adding the data according to the max value of column "bill month"

Help
1

hello guys need help with something.

I have filtered on the base of similar Account Name and same Ban.

Now, i want to select only that value in Actual Balance Amt column which has Max value of Bill Month

Please check the image given below::

image
image1035×582 124 KB

2

Hey @AryanSingh,

Use the excel application scope and read range activity which will return you a datatable.

Then use the expression below:

dt.AsEnumerable().OrderByDescending(function(f) f.ColunName)(0).item(“your Column Name”).ToString()

3

@amarasto

dt.AsEnumerable().OrderByDescending(function(f) f.ColunName)(0).item(“your Column Name”).ToString()

i am confused that which column name should be entered after f. and after item(

so, this command will let me retrieve the Actual Balance Amt of the Max value of bill month, right??

4

Use Actual Balance Amt in place of column name.

5

@amarasto

dt.AsEnumerable().OrderByDescending(function(f) f.ColunName)(0).item(“Actual Balance Amt”).ToString()

Please check this? n suggest me the correction?

6

Hey @AryanSingh
dt.AsEnumerable().OrderByDescending(function(f) f.Actual Balance Amt )(0).item(“Actual Balance Amt”).ToString()

7

Hi @AryanSingh

I’ll suggest you to follow these steps

  1. Use below Linq query in assign —> to make groups of same Ban’s and get Max of Bill Month from those groups

where List1 will hold max of Bill Month according to groups of Ban’s.
and dta is your excel sheet DataTable.

  1. Use Lookup DataTable activity in loop to take out Actual Balance Amt values according to List1 (All those max values of Bill Month)

For your better understanding I’m attaching my sample workflow here: GroupByANDmax.xaml (12.7 KB)
So check the Build DataTable to see Input DataTable, then run it and check the output. :slight_smile:

8

@samir
@amarasto

Thanx both. Let me try.