How to compare Datatables with values

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.

@smritijoshi
welcome to the forum

It can be handled as a full join case

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

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

1 Like

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

In addition to Raja

for the reference ( introducing to a Full Join Demo)

1 Like

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.

Hello @smritijoshi

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

Hello!

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

@smritijoshi

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

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

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
grafik

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.

Okay. I will try this solution.

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

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

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?

@riyatona18

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.

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

@smritijoshi asked about the interpretation of the join result

we mentioned full join to use

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)

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