Filter table by the latest date for each currency

Help Activities
, ,
1

Hello,
i have table of FX rates for the last five days, i need to filter the list and keep only the rows with latest date for each currency, and delete the older rows.

Thank very much for the help

image

1 Like
2

Hi @Alsabbagh_Hussam

Can you please elaborate the workflow explanation so it will be easy to understand. please provide the excel file also.

Regards,

3

Hi @Alsabbagh_Hussam

Sort the DataTable based on the date column in descending order. This will ensure that the latest dates appear at the top of the table.Create variables to store the current currency and the latest date.

Use a For Each Row activity to iterate through each row in the sorted DataTable.Inside the loop, compare the current currency with the stored currency variable. If they are different, update the stored currency and latest date variables with the values from the current row.

Inside the loop, use an If activity to check if the date in the current row matches the latest date. If it doesn’t match, use the Remove Data Row activity to delete the row.
After the loop, use the Write Range activity to write the updated DataTable back to the file or data source.

I hope it helps!!

4

if you can provide sample file it will be easy to give the exact solution

as it can be done with linq by grouping the two columns and ordering by decending and selecting the first

Regards

5

Hello Parvathy,
please find attached the file
11 05 2023 FX rates.xlsx (21.7 KB)

6

Hello Lakshmi,
please find attached the file
11 05 2023 FX rates.xlsx (21.7 KB)

7

try this using assign activity

From r In dt.AsEnumerable
Group r By a=r("fr").ToString,b=r("to").ToString Into grp=Group
Select grp.OrderByDescending(Function(x) CDate(x("Valid fr").ToString)).first()).CopyToDataTable

Edit : filter initial datatable to remove last row with fr column not empty

so the flow looks like below

image
image900×676 23.7 KB

hopes this helps

Regards

8

Hi, Thank you Lakshmi,
however , it seem the expression is incorrect
Could you please make a screenshot for the expression editor of the assign or if possible send as xaml file ?

image
image502×504 12.6 KB

9

sorry my bad i missed out one bracket

(From r In dt.AsEnumerable
Group r By a=r("fr").ToString,b=r("to").ToString Into grp=Group
Select grp.OrderByDescending(Function(x) CDate(x("Valid fr").ToString)).first()).CopyToDataTable

also find the file
GroupDtfirst.xaml (7.2 KB)

regards

1 Like
10

Hi :slight_smile:
The xaml coull not open

image
image694×280 10.4 KB

however, the expressions did work but i am getting this error with it
image

Thanks Hussam

11

that is the reason i have mentioned in previous reply to use filter datatable activity to remove last row which doesnot have values related to the columns

use filter datatable activity , in that use column either fr or to and condition as not empty
give same datatable variable for filtered table as well

you can refer screen shot as well

Regards

1 Like
12

Thank you Lakshmi for the great help

1 Like
13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.