Compare first column of two datatables

Help Studio
, ,
1

I would like to campare the first column of two datatables, find items that are only in dt1 and not in dt2, copy the whole row of those uncommon items and save them to another excel file, while also deleting the common rows in dt1.
Plz help, thank you.

2

Join Data Table into finalDT
For Each row in finalDT

  • If column from dt2 is empty, Add Data Row to newDT

Now newDT will contain the rows that are only in dt1.

Then you can Filter Data Table on finalDT to remove the rows that have a value in that column from dt2, with the destination datatable being dt1.

3

The problem is I don’t know what expression to do achieve those steps.

4

They aren’t expressions. They’re activities…Join Data Table, For Each Row in Datatable, Add Data Row, Filter Data Table.

5

But the column wouldn’t be empty though

for example dt1 has:
apple 123 abc
bee 234 efg
cat 567 hij

while dt2 only has:
apple 123 abc
cat 567 hij

but I only want to complare the first column, so I should get only the row:
bee 234 efg

and if possible, I would like to not use for each…

6

If you Join Data Table dt1 and dt2 you’ll end up with…

apple 123 abc apple 123 abc
bee 234 efg
cat 567 hij cat 567 hij

Which gives you everything you need to move “bee 234 efg” to a separate datatable and then filter it out of the final datatable.

If you don’t want to use For Each (even though there’s no reason not to) you could use Filter Datatable once to create a dt of non-matching rows (ie the fourth column is blank) and then a second time to create a dt of matching rows (ie the fourth column is not blank).

7

So how can I check whether there’s two times like “apple 123 abc apple 123 abc”
or only one time like “bee 234 efg”?
Sorry I’m just an intern and is new to this

8

With Filter Data Table.

Assuming these two tables:

image

image

Do the Join (I did it into a table named dt_joined)

image

Then to find common rows:

image
image566Ă—551 17.3 KB

(the left column is “Column1_1”)

Use the Output Columns tab to keep only the original columns because after the Join you’ll have Column1, Column2, Column3, Column1_1, Column2_1, and Column3_1 (the _1 columns are the ones from dt2 because they have the same name in both datatables)

image
image561Ă—550 17.9 KB

Then do another filter to find uncommon rows:
image
(the left column is again “Column1_1”)

Result after running this…

dt_commonRows:

[Column1,Column2,Column3
apple,123,abc
cat,567,hij
]

dt_uniqueRows:

[Column1,Column2,Column3
bee,234,efg
]
1 Like
9

Note that I edited my reply because I had said the left column in the Filters is Column1, but it’s actually Column1_1 (Column1 from dt2). If it’s empty, the value didn’t match from dt1 to dt2.

1 Like
10

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