How to compare two data tables and get all rows from dt1

Help Activities
, ,
1

Hi,
Having 2 data tables. I need to compare the 2 data tables with below conditions

  1. ID columns values should be equal
    2.Date column value from dt1 should be between date1 and date2 from dt2(date>=date1 and date<=date2)
    finally output dt get all rows from dt1+matching rows from dt2 like below.

Ex: dt1

ID Date
123 12/27/2024
124 12/28/2024
125 12/29/2024
126 12/30/2024
127 12/31/2024
128 1/1/2025

Dt2:

ID Date1 Date2 Comments
123 12/26/2024 12/27/2024 Testing
124 12/27/2024 12/28/2024 Development
125 12/25/2024 12/29/2024 Prod Move

Output dt:

ID Date Date1 Date2 Comments
123 12/27/2024 12/26/2024 12/27/2024 Testing
124 12/28/2024 12/27/2024 12/28/2024 Development
125 12/29/2024 12/25/2024 12/29/2024 Prod Move
126 12/30/2024
127 12/31/2024
128 1/1/2025

TIA

2

Hi,

  1. Create a new DataTable variable, dtOutput
  2. Use assign activity and write the expression in RHS
(From row1 In dt1.AsEnumerable()
Group Join row2 In dt2.AsEnumerable()
On row1("ID") Equals row2("ID") Into GroupJoin = Group
From subRow2 In GroupJoin.DefaultIfEmpty()
Select dtOutput.Rows.Add(
    row1("ID"), 
    row1("Date"), 
    If(subRow2 Is Nothing, Nothing, subRow2("Date1")),
    If(subRow2 Is Nothing, Nothing, subRow2("Date2")),
    If(subRow2 Is Nothing, Nothing, subRow2("Comments"))
)).CopyToDataTable()
1 Like
3

@Manaswini_UI

  1. Join dt1 and dt2 using join dtatable on ID with left join
  2. Now after join you can use linq or filter to get the rows only between required date1 and date2

sample linq to be used in assign joineddt = joineddt.AsEnumerable.Where(function(x) String.IsNullOrEmpty(x("Date1").ToString) OrElse (Cdate(x("Date").ToString)>=Cdate(x("Date1").ToString) AndAlso Cdate(x("Date").ToString)<=Cdate(x("Date2").ToString))).CopyToDataTable

cheers

1 Like
4

Tried joining dt1 and dt2 using left join but its giving more than the number rows in dt1

5

@Manaswini_UI

Ideally if ypu do a left join on id and assuming id is unique you would get same number as dt1

Can you check if there are any repeating rows

Cheers

6

dt.AsEnumerable.Where(Function(x) String.IsNullOrEmpty(x(“Date1”).ToString) OrElse ((x.Field(Of DateTime)(“Date”).ToString)>=(x.Field(Of DateTime)(“Date1”).ToString) AndAlso (x.Field(Of DateTime)(“Date”).ToString)<=(x.Field(Of DateTime)(“Date2”).ToString))).CopyToDataTable

following your steps, for 2nd step while comparing the datetime(MM/dd/yyyy hh:mm:00) above linq query not working in UiPath windows legacy platform(getting Specific cast invalid error) and same query working and getting expected results in UiPath windows platform. Could you please suggest in this.

Note: Used same version of system, excel activities.
TIA.

7

@Manaswini_UI

try to check how the data format is coming

cheers

8

Date column coming in custom type in excel and other date1 and date2 are in General type. Could you please suggest how to change date column to general type?

9

@Manaswini_UI

Use format cells activity

Cheers

10

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