aksh1yadav
(AKSHAY YADAV)
February 13, 2018, 5:38pm
1
Hello Community
Use Below Code to Compare 2 Datatable Column and Get Matched and Not Matched Records
Note- Based on your datatable column datatype update datatype here with column field.
To Get Matched Records
Datatable Out_Matched_Data = In_DataTable1.AsEnumerable().Where(function(row) In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()
To Get Not Matched Records
Datatable Out_NonMatched_Data = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()
Component -
Component for Two Datatable Column Match and Get The Matched Records.xaml (5.9 KB)
Component for Two Datatable Column Match and Get The Non Matched Records.xaml (5.9 KB)
Sample Workflow file to use above reusable component - datatable column match.xaml (12.3 KB)
Regards…!!
Aksh
27 Likes
SELECT *
FROM class1 c1
JOIN class2 c2 ON c1.groupName = c2.groupName
AND c1.subgroup = c2.subgroup
WHERE
(
SELECT COUNT(DISTINCT ind)
FROM class2 c2a
WHERE c2a.groupName = c1.groupName
AND c2a.subgroup = c2a.subgroup
) = 2
AND
(
SELECT COUNT(DISTINCT subgroup)
FROM class1 c1b
WHERE c1b.groupName = c1.groupName
) =
(
SELECT COUNT(DISTINCT subgroup)
FROM class2 c2b
WHERE c2b.groupName = c2.groupName
)
Try using this SQL query to get wat u want
1 Like
Where exactly do the first two written codes go in the first post? I don’t know where to put my excel columns in the sample workflow.
Thanks!
-Anna
1 Like
What if there are more than 2 Columns to be compared, can we have something for that?
Thanks in advance
2 Likes
@aksh1yadav
Could you please help us with answer for the question asked above
1 Like
aksh1yadav
(AKSHAY YADAV)
September 26, 2018, 5:06am
6
Hey @PrankurJoshi and @Sreelatha278
Yeah we have… and i am too busy so missed things and notifications.
Will share my existing sample when will be on my laptop
I have a sample script already for you guys.
Regards…!!
Aksh
4 Likes
cheersrpa
(cheersrpa)
September 27, 2018, 9:34am
7
hey @aksh1yadav
Can you please share the sample .xaml file
1 Like
@aksh1yadav
USing 2 excel sheets as 2 datatables…
getting error (non matched data) : Specified cast is not valid…
1 Like
I initially had the same problem. Error can be resolved by adjusting the type of the field extension method, in my case from in32 to double as follows:
In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Double)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Double)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()
And then it works!!! Thanks Aksh for sharing this component.
2 Likes
Ananth_K
(Ananth K)
November 22, 2018, 2:29pm
10
@aksh1yadav , thank you so much for the logic.you made my day.
1 Like
Hi Aksh1yadav,
Thanks for this code.
However, I am getting this error when I use it with my datatables.
Source: Assign
Message: Specified cast is not valid.
The error is from the [Component for Two Datatable Column Match and Get The Matched Records Assign activity.
Please, can you explain why?
Thanks in advance.
aksh1yadav
(AKSHAY YADAV)
January 13, 2019, 8:42am
13
Hey
Because the column datatype is different in your case so change that accordingly im Field< of your datatype of columo>
Regards…!!
Aksh
1 Like
I used this code below to display my column datatype:
DT1.Columns(“Column1”).DataType.Name.ToString()
The result is “Object”.
When I replace Int32 to Object in the original code, it gives validation errors.
Is Object a valid column datatype?
Thanks in advance
1 Like
shilpapmk
(shilpa)
January 14, 2019, 2:34am
15
mathieubelt:
AsEnumerable
Hey !
Could you pls share the sample xaml file, want to know how to use the above code i see many ppl suggesting such code , pls let me know how to use such code instead of activities.
thanks in advance
aksh1yadav
(AKSHAY YADAV)
January 14, 2019, 6:38am
16
Hey @siseneg_nina
siseneg_nina:
When I replace Int32 to Object in the original code, it gives validation errors.
Is Object a valid column datatype?
Use Field(Of String) in your case please
Regards…!!
Aksh
aksh1yadav
(AKSHAY YADAV)
January 14, 2019, 6:39am
17
Hey @shilpapmk
it is already there in the post
Let me know in case if you are still facing any problem.
Regards…!!
Aksh
1 Like
Hey @aksh1yadav
Thanks its fine now… i had to convert the rows of the data table to string. It worked with Field(Of String) after the conversion.
Thank you very much!
aksh1yadav
(AKSHAY YADAV)
January 16, 2019, 5:19am
19
Hey @siseneg_nina
Even without column type conversion Field(Of String) will work but anyway if you have done
Regards…!!
Aksh
1 Like
tgopalas
(Tanuja)
January 16, 2019, 11:41am
20
Its not working when I’m taking the data from Excel. Even though there is matched data it is saying source contain no data rows. Can you please advice on this?
aksh1yadav
(AKSHAY YADAV)
January 16, 2019, 12:24pm
21
Hey @tgopalas
Will you share your sample excel?
Regards…!!
AKsh