How to compare Datatables with values

[smritijoshi (Smritijoshi2709)]

Hello there,

I’ve been trying to compare two datatbles. As the datatable is huge, using for each row is not feasible.

DT1:

Name	User	Machine	T	Role
Joey	Joey01	Machine1	ABC	Admin, Developer
Ross	Ross02	Machine2	ABC	Admin, Robot
Chandler	Chandler03	Machine3	ABC	Developer, robot
Monica	Monica04	Machine4	ABC	Robot
Rachel	Rachel05	Machine5	ABC	Developer, Robot

DT2:

Name	User	Machine	T	Role
Joey	Joey01	Machine1	ABC	Admin, Developer
Ross	Ross02	Machine2	ABC	Admin, Robot
Monica	Monica04	Machine4	ABC	Robot
Rachel	Rachel05	Machine5	ABC	Admin, Developer, Robot
Phoebe	Phoebe06	Machine6	ABC	Admin, Developer, Robot, Control manager

Expected Output DT3:

Name	User	Machine	T	Role	Remarks
Joey	Joey01	Machine1	ABC	Admin, Developer
Ross	Ross02	Machine2	ABC	Admin, Robot
Chandler	Chandler03	Machine3	ABC	Developer, Robot	Removed user
Monica	Monica04	Machine4	ABC	Robot
Rachel	Rachel05	Machine5	ABC	Developer, Robot	Removed Admin role
Phoebe	Phoebe06	Machine6	ABC	Admin, Developer, Robot, Control manager	Added new user

Is there any other through which this can be achieved?

Please help me out. Any leads will be highly appreciated.

[ppr (Peter Preuss)]

@smritijoshi
welcome to the forum

It can be handled as a full join case

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…

Join cols: User (optional additional Join Cols)
Join Type; Full Join

Then based on the Join Cols we can detect User Addtions /Removals (col is null)
The roles we can split row(“Role”).Split(","c).Select(Function (x) x.Trim).ToArray
The split Arrays we can anaylse with the Intersect and Except Operators

[HowTo] - Exploring the LINQ Universe (VB.Net)

LINQ (Language-Integrated Query) is part of the .NET framework and offers capabilities for interacting with data, objects, or other sources within a particular syntax. This HowTo elaborates on the availability of LINQ and explores the offered functionalities Current Version of this topic is DRAFT and will be finalized / enhanced soon Introduction Let’s recap the Example of LINQ from [HowTo] - First Start with LINQ (VB.Net). Sample data: myNumbers | List (Of Int32) = {12,34,5,8,10,2,15,7}.to…

Let us know your further open questions once YOu have started to explore a Full Join with the Join DataTAble Activity

[raja.arslankhan (Raja Muhammad Arslan khan)]

Join_1Col_MergeBoth_AddCols.xaml (11.0 KB)
Please change according your requirements

[ppr (Peter Preuss)]

In addition to Raja

@raja.arslankhan

Join_1Col_MergeBoth_AddCols.xaml (11.0 KB)
Please change according your requirements

for the reference ( introducing to a Full Join Demo)

How to join two datatables and get duplicate and non duplicate items in column 1 from both datatables

@Karan28 Find some quick prototype as starter help here, based on a generic dataset Join_1Col_MergeBoth_AddCols.xaml (11.0 KB) Just give a play by renaming the column names as by your case. check also the ColList variable, for configuring the final result list columns Constraints: Join Col to keep is the first column from Join DataTable A later more dynamizing should be possible

[smritijoshi (Smritijoshi2709)]

Thanks for quick response @ppr & @raja.arslankhan

After using full join I’m getting below output:

Name	User	Machine	Tenant	Role	Name_1	User_1	Machine_1	Tenant_1	Role_1
Joey	Joey01	Machine1	ABC	Admin, Developer	Joey	Joey01	Machine1	ABC	Admin, Developer
Ross	Ross02	Machine2	ABC	Admin, Robot	Ross	Ross02	Machine2	ABC	Admin,robot
Monica	Monica04	Machine4	ABC	Robot	Monica	Monica04	Machine4	ABC	Robot
Rachel	Rachel05	Machine5	ABC	Developer, Robot	Rachel	Rachel05	Machine5	ABC	Admin, Developer, Robot
Phoebe	Phoebe06	Machine6	ABC	Admin, Developer, Robot, Control manager	Phoebe	Phoebe06	Machine6	ABC	Admin, Developer, Robot, Control manager
Chandler	Chandler03	Machine3	ABC	Developer, Robot

Cols are repeated.

[Rahul_Unnikrishnan]

Hello @smritijoshi

Please cehck whether the below video can help you in this.

UiPath- How to Compare two Data Tables| Output the similar and dissimilar rows |Faster method

[smritijoshi (Smritijoshi2709)]

Hello!

I cannot attach the xaml file as I’m new to this forum. The output which I’m getting is as above.

[ppr (Peter Preuss)]

@smritijoshi

@smritijoshi

After using full join I’m getting below output:

@smritijoshi

Cols are repeated.

No, there are joined _1 cols are common col name, but indicating that there are from right datatable

perfect, the join result which you will post process in the next step

@ppr

Then based on the Join Cols we can detect User Additions /Removals (col is null)

Chandler, right side join col e.g. Name_1 is blank - so it is a removal

@ppr

The roles we can split row(“Role”).Split(","c).Select(Function (x) x.Trim).ToArray
The split Arrays we can analyze with the Intersect and Except Operators

As mentioned we can handle the Role Additions / Removals

Kindly Note:
We do mention using Intersect / Except on the Roles when split to Array/List… like

We are NOT talking about using Intersect / Except on Datarow level as shown in the video from Rahul, as this will not work on Role Consolidations and also not on User Addition / Removal analysis.

[smritijoshi (Smritijoshi2709)]

Okay. I will try this solution.

Although, what if there is any new user added to DT2? @ppr

[ppr (Peter Preuss)]

Name value wiil null/blank And Name_1 Value will be not blank/null

[riyatona18 (Riyatona)]

Okay @ppr

Got this part. But the row count of the datatable is showing less than actual.
As I’m trying to use for each now the rows which have no data in first or second part is not counted. Only the ones which are completely matching are counted.

Am I missing something here?

[ppr (Peter Preuss)]

@riyatona18

@riyatona18

But the row count of the datatable is showing less than actual.

a more clear referencing of the parts from the modeling int the description would help us for better understanding. Maybe you will share some details with us.

[riyatona18 (Riyatona)]

I’m facing similar issue like @smritijoshi
I think it’s due to inner join. Thanks!

[ppr (Peter Preuss)]

@riyatona18

I’m facing similar issue like @smritijoshi

@smritijoshi asked about the interpretation of the join result

@riyatona18

I think it’s due to inner join

we mentioned full join to use

@ppr

Join cols: User (optional additional Join Cols)
Join Type; Full Join

[kumar.varun2 (Varun Kumar)]

Hi @smritijoshi

Can you please clarify, in the output DT3, for the user Rachel05, in the remarks, the Admin roles is indicated as ‘removed’, but it should be ‘added’ instead.

How are you obtaining the output DT3, and specially the remarks?

@smritijoshi Can you give this a try

DT_CompareDTWithValues.zip (25.7 KB)