Get latest date from data table and update in another table

Help Studio
1

Dt1:

image
image520×555 118 KB

Dt2:

image
image408×568 108 KB

There will be multiple dates for each user in dt2 datatable for each user.

How to get only the latest date (Highlighted in Green) for each users from dt2 and update in the “Date of Last Locking” column in dt1 in the respective user row

2

@Sathish_Kumar_S

Please follow the steps

  1. Use a assign activity dt2 = dt2.AsEnumerable.OrderByDescending(function(x) Cdate(x("Date").ToString)).CopyToDataTable
  2. Now use for each row in datatable…on dt1
  3. Inside the loop use a filter datatable on dt2 and filter with the name column "User" equals currentrow("User").ToString and save in filtereddt table
  4. Now use assign after filter datatable activity currentrow("Date of Last locking") = filtereddt.Rows(0)("Date").ToString to get latest date

Hope this helps

Cheers

3

Hi @Sathish_Kumar_S ,

Can you share a sample file? So that we can check on.

4

Hi @Sathish_Kumar_S ,

An alternate way :

  1. Perform a Group By operation on the DT2 and keep only the rows with the Latest date for users. It can be done using the below Query :
GroupedDT = DT2.AsEnumerable.GroupBy(Function(x)x("User").ToString).Select(Function(x)x.OrderByDescending(Function(y)CDate(y("Date").ToString)).First).CopyToDatatable

Here, GroupedDT is a variable of type Datatable which contains the Users with their Latest dates, DT2 is your Input DT2.

  1. Next, we can use Join Datatables Actviity with the First datatable as DT1 and Second Datatable as GroupedDT. Join Type as Left Join. The column to match should be entered as "User".
    The data from the GroupedDT should be concatenated with the matching rows with the DT1 data rows.
    image

  2. The above would not update the column Date of Last Locking but we can rename the column Date_1 in the resultant datatable to ‘Date of Last Locking’ using the below Expression in an Assign Activity.

DT1.Columns("Date_1").ColumnName = "Date of Last Locking"

Let us know if you were able to implement this method or facing any issues.