smritijoshi
(Smritijoshi2709)
September 3, 2022, 3:25pm
1
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)
September 3, 2022, 3:48pm
2
@smritijoshi
welcome to the forum
It can be handled as a full join case
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
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
1 Like
Join_1Col_MergeBoth_AddCols.xaml (11.0 KB)
Please change according your requirements
ppr
(Peter Preuss)
September 3, 2022, 4:03pm
4
In addition to Raja
for the reference ( introducing to a Full Join Demo)
@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
1 Like
smritijoshi
(Smritijoshi2709)
September 3, 2022, 4:52pm
5
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.
smritijoshi
(Smritijoshi2709)
September 3, 2022, 5:46pm
7
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)
September 3, 2022, 6:09pm
8
@smritijoshi
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
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
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)
September 3, 2022, 7:08pm
9
Okay. I will try this solution.
Although, what if there is any new user added to DT2? @ppr
ppr
(Peter Preuss)
September 3, 2022, 7:26pm
10
Name value wiil null/blank And Name_1 Value will be not blank/null
riyatona18
(Riyatona)
September 3, 2022, 8:37pm
11
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)
September 3, 2022, 9:18pm
12
@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.
riyatona18
(Riyatona)
September 3, 2022, 9:30pm
13
I’m facing similar issue like @smritijoshi
I think it’s due to inner join. Thanks!
ppr
(Peter Preuss)
September 3, 2022, 9:34pm
14
@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)
system
(system)
Closed
September 10, 2022, 9:17am
16
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.