Compare Date columns in two Datatables using Linq

Help Studio
, , , ,
1

Hi,
I have two datatables for exp :- DT1 and DT2. DT1 has one column with Datetime type and DT2 also has one column with Datetime type.
Now I need to compare and check which datetime column is greater one and store rows of the greater column value’s Datatable in a third datatable (say DT3) using LINQ Query.

Any suggestion on this ?

2

Hi @Debartha_Mitra_DE,

Share sample input file and expected output file. it may help us to provide you better solution.

thanks,

3

thanks for reply.
Please find the details of two datatables below (took it from debug mode)

DT1 has values like below
[Datum,Filename
02/23/2022 00:00:00,218185_002_Web_BMK.pdf
02/23/2022 00:00:00,Peloton Annual Report Bookmarked.pdf
]

DT2 has values like below
[Datum,Filename
11/03/2022 00:00:00,2020_The_Boeing_Company_Annual_Report.pdf
11/03/2022 00:00:00,218185_002_Web_BMK.pdf
11/03/2022 00:00:00,annual-report-2020-en.pdf]

Now, i need to compare these Datum column (datetime type) only when the filenames in both datatables are equal and then find values which has latest date (or greater datetime value) and add to third Datatable.

Now I need to use only Linq here.

Let me know.

4

Hey!

With linq
have you tried to merge your DT2 into your DT1, sort your table by Datum and then group by Filename?

Using the Sort DataTable from the uipath to update the DT1 and then using the LINQ:

DT3 = DT1.AsEnumerable.GroupBy(Function(x) x("Filename").ToString).Select(Function(y) y.First).CopyToDataTable

DT1
image


DT2
image


DT1 after merge
image


DT3 after the LINQ code to group by Filename
image

SampleLinq.zip (56.7 KB)
Text me if you need more help :slight_smile:

1 Like
5

Thanks for reply and workflow.
So how will I know which filename is common one and has latest date from DT3 ?

See, I need to get the filename which is common in both datatables (DT1 and DT2) and that filename must have latest date (or greater date).
As you can see “218185_002_Web_BMK.pdf” is common one but the date format is different in both datatables (for example, in DT1 it is 02/23/2022 4:00:00 AM and in DT2 it is 11/03/2022 04:00:0). In the first one date format is MM/dd/yyyy and in the second one it is dd/MM/yyyy, so how will compare these dates and get the latest dated filename ?

FYI, in my scenario the DT1 and DT2 may have multiple files with same name and I need to get multiple files with greater date or latest date in that case.
It will be better if I can save only common files with latest date (from both DT1 and DT2) in DT3 datatable.

6

If you order by date you’ll put your newest files on top.
If you group by name, keeping only the First appearence of the File you’ll preserve on your new DT only teh newest file, because you ordered it by date, from newest to oldest.

Your DT1 will always have Date Format MM/dd/yyyy and your DT2 will always have the dateformat dd/MM/yyyy?
If its a yes, i think you should first parameterize your datum and then use the approach that i told before.

So:
Read DT1, DT2
Build DT3
Parametrize Datum from DT2 to MM/dd/yyyy or parametrize DT1 to dd/MM/yyyy
Use the approach that i told before → merge, order by date desc, group by, remove duplicates
DT1
image
DT2
image
DT3 after run
image

SampleLinq.zip (57.0 KB)

Or you only want on your DT3 duplicated Filenames? Removing the nonduplicated values?