Getting unique values out of two data tables

[JennZabba]

Hello:

I have two data tables with the same structure (the number of columns is the same and the name of columns are the same)

My first data table is named datatable1 and my second data table is named: datatable2

What I need is:

I need to compare these two data tables using three columns, and if the rows in these columns are the same then I need to delete them so the expected outcome is to have unique rows in new datatable 3 by comparing only 3 columns.

Could you please help me with this I’m very stuck and I need detailed instruction.

[Steven_McKeering]

Hello

Can you provide the name of the 3 columns?

In the meantime, take a look at this reply from the great @ppr

How to remove common rows from data tables

@EMREUYSAL find starter help here: DataTable_SetOperations.xaml (8.9 KB) was done with the set operations Intersect And Except: Another techniques is to do it with Any and denied Any in combination sequenceEqual (have also a look here on the use of sequenceequal - [HowTo] - Find Duplicate / NonDuplicate rows - checking all columns - without explicit listing the colnames/index ) to safe it up it is recommended to do the empty result check similar as below [grafik]

You could also try a “Join datatable” activity and then remove “remove duplicate” rows activity.

Cheers

Steve

[lrtetala (Lakshman Reddy)]

Hi @JennZabba

Try this

uniqueRows = (From row1 In datatable1.AsEnumerable()
Where Not datatable2.AsEnumerable().Any(Function(row2) row1(“Column1”).Equals(row2(“Column1”)) And
row1(“Column2”).Equals(row2(“Column2”)) And
row1(“Column3”).Equals(row2(“Column3”)))
Select row1).CopyToDataTable()

Hope this helps!!

[JennZabba]

but I need this to be in a data table, my expected outcome needs to be a data table

[JennZabba]

Hello, this is what I need:

Column names lets call them col 1 col 2 and col 3, can you help please?

[ppr (Peter Preuss)]

We can work with Join Data Table Activity and postproces the Join Result

Or Using some Linqs - Match Approach

Assign Activity - Just adapt to your Column names
arrColSet = {“Column1”,“Column2”,“Column3”}

Assign Activity:
Dt1OnlyRows | List(Of DataRows) =

(From d In datatable1 .AsEnumerable()
Let lcs = arrColSet.Select(Function (x) d(x).ToString.Trim)
Let chk = datatable2.AsEnumerable().Any(Function (d2) arrColSet.Select(Function (x2) d2(x2).ToString.Trim).SequenceEqual(lcs))
Where Not chk
Select r = d).ToList

Assign Activity:
Dt2OnlyRows | List(Of DataRows) =

(From d In datatable2 .AsEnumerable()
Let lcs = arrColSet.Select(Function (x) d(x).ToString.Trim)
Let chk = datatable1.AsEnumerable().Any(Function (d2) arrColSet.Select(Function (x2) d2(x2).ToString.Trim).SequenceEqual(lcs))
Where Not chk
Select r = d).ToList

Finaly when there are rows returned
Assign Activity
dtUniqueRows | DataType: DataTable =
Dt1OnlyRows.Concat(Dt2OnlyRows ).CopyToDataTable

[ppr (Peter Preuss)]

A more unusual approach, but also working one could be the Group By Approach

Assign Activity
dtMerge = datatable1 .Copy()

Merge DataTable Activity| Merge datatable2 to dtMerge

Assign Activity
dtUniqueRows | DataType: DataTable =

(From d in dtMerge.AsEnumerable()
Group d by k1=d("Column1").ToString.Trim,k2=d("Column2").ToString.Trim,k3=d("Column3").ToString.Trim into grp=Group
Where grp.Count = 1
Select r = grp.First()).CopyToDataTable

Handling empty result we can do:

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.As…

[ppr (Peter Preuss)]

@JennZabba

Hello, this is what I need:

here it shows the common rows (as yellow is present in both)

But initial was told:

@JennZabba

so the expected outcome is to have unique rows

Maybe you clear the requirements

BTW: for the Match Approach LINQ we would only modify the check / filter condition

Also have a look here:

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…

[Anil_G (Anil Gorthi)]

@JennZabba

You can use this

first merge the datatables together

using merge datatable actvity into one datatable say mergeddt

then use mergeddt = mergeddt.AsEnumerable.GroupBy(function(x) x("FirstColumn").ToString+x("SecondColumn").ToString + x("ThirdColumn").ToString).Where(function(x) x.Count=1).SelectMany(function(x) x).CopyToDataTable

cheers