DataTables - update a row based on another datatable

[adrian_sullivan]

hi all - hoping that someone can help me with something

i have a work flow that has two data tables. if a field “FileCode” is in both data tables i need it it to updated the the processed flag in the other to Y

so one big data table DT1

one small data table DT2

As file code 5555 is in both i would need the processed flag in DT2 to be set to Y

not certain how to do this - a join will not work as the second row in DT2 will not be picked up

any help greatly apprecaited

adrian

full xaml also attached

Main.xaml (13.3 KB)

[Shiva_Nikhil]

@adrian_sullivan

can you try this in invoke code activity

For Each r As datarow In dt2.AsEnumerable
If dt1.AsEnumerable.Any(Function(a) (a("FileCode").ToString).trim.equals(r("FileCode").ToString.Trim)) Then
	r("Processed")="Y"
Else
	r("Processed")="N"
End If
Next

[adrian_sullivan]

@Shiva_Nikhil

For Each r As datarow In dt2.AsEnumerable
If dt1.AsEnumerable.Any(Function(a) (a("FileCode").ToString).trim.equals(r("FileCode").ToString.Trim)) Then
	r("Processed")="Y"
Else
	r("Processed")="N"
End If
Next

that is great that worked for the solution j have here - always amazed that i can spend days trying to solve something. put the issue here and it is solved in minutes. now to try it on the bigger \ real problem. will update you again shortly

[Schirru]

@adrian_sullivan,

If you want to maybe use a LINQ statement with the Join statement, you can give it a try with the following:

Dim query =
            From row1 In dt1.AsEnumerable()
            Join row2 In dt2.AsEnumerable() On row1.Field(Of String)("FileCode") Equals row2.Field(Of String)("FileCode")
            Select row2

        ' Execute the query to get the matching rows and update the "Processed" column
For Each matchedRow In query
    matchedRow.SetField("Processed", True)
Next

Use it inside an invoke code passing the datatables as IO arguments.

Cheers,

[ppr (Peter Preuss)]

@adrian_sullivan

a join will not work as the second row in DT2 will not be picked up

Could be handled by a full join

Story line of Join Data Table

Dear innovator’s, All we know that there is a Join Data table activity in UiPath.If we get any matching scenarios with unique value will approach join data table activity instead of filter/loop/array matching activity to find an exact match. Join Data Table: [image] Combines rows from two tables by using values common to each other, according to a Join rule, which is specified in the Join Type property. Sample Table: [image] INNER Join: The INNER JOIN keyword selects records that have m…

lets exclude more details on trimming, harmonizations when e.g. working with the Join DataTable

[adrian_sullivan]

that worked great on the larger version as well

thanks again

[mkankatala (Mahesh Kankatala)]

Hi @adrian_sullivan

You can use the LinQ Expression to update the processed column with Y, If condition got validated.
→ Use Read range workbook activity to read the Excel1 and store in a datatable variable called dt1.
→ Use another Read range workbook activity to read the Excel2 and store in a datatable variable called dt2.
→ After read range use the for each row in datatable activity to iterate the each row in dt1.
→ Inside for each insert the assign activity and give the expression as below,

CurrentRow("Processed") = If(dt1.AsEnumerable().Any(Function(dr) dr("FileCode").ToString = CurrentRow("FileCode").ToString), "Y", CurrentRow("Processed").ToString)

→ Outside foreach insert the write range workbook activity to write the dt1 datatable to excel.

Check the below workflow for better understanding,

Workflow -
2024.xaml (15.8 KB)

Output -

Hope it helps!!