I have two datatables with multiple columns but they have two common columns “Plant Code” and “Supplier ID” , I want to check row by row , if the “Plant Code” and “Supplier ID” from the First datatable , if this combination of “Plant Code” and “Supplier ID” from the first datatable exists in the second datatable with the check line by line in the second datatable , here is an exemple :
Datatable 1 :
Datatable 2 :
hi @aya.lajili ,
If you want to compare both the data Tables line by line,
first sort both of the data table using:
sortedDT1= DataTable1.AsEnumerable().OrderBy(Function(x) x(“SupplierID”)).ThenBy(Function(x) x(“PlantCode”))
sortedDT2= DataTable2.AsEnumerable().OrderBy(Function(x) x(“SupplierID”)).ThenBy(Function(x) x(“PlantCode”))
Check if both the datatable has equal number of rows, if it has,
use a while loop with condition counter<sortedDT1.RowCount
use an if condition stating (sortedDT1.Rows(counter)(“SupplierID”).ToString.Trim.equals(sortedDT2.Rows(counter)(“SupplierID”).ToString.Trim)) And (sortedDT1.Rows(counter)(“PlantCode”).ToString.Trim.Equals(sortedDT1.Rows(counter)(“PlantCode”).ToString.Trim))
increment counter by 1 after exiting if
Use a flag variable to see if it doesn’t matches, and in the end check for that flag if it’s set to True.
P.S. : Please check the number of Brackets, wrote this expression here, it might throw an error. But could be programmed correctly while building bot.
Yes , it is not necessary to have the two datatables have the same rows count since the second datatable presents a Blacklist we will check if each (line by line) combination of “PlantCode” and “Supplier ID” from the first Datatable exists in the second table if it is not , it will be not in the blacksit
Please find below xaml
Main.xaml (17.3 KB)
Try these steps in Order to Achieve this Automation:
- Read Both the Data Table in DT_1 and DT_2.
- Create a Variable of Name “Idx” of type integer and put its default value to 0.
- Use “While” Activity and pass DT_1.Rows.Count>idx.
- Use “IF” Activity and in Condition Pass this in it.
DT_1.Rows(idx)(“SupplierID”).ToString = DT_2.Rows(idx)(“SupplierID”).ToString And
DT_1.Rows(idx)(“PlantCode”).ToString = DT_2.Rows(idx)(“PlantCode”).ToString
- After If Actitvity, Use Assign Activity and Increase the Idx Value by 1.
Hope this Helps.
Thanks a lot and for u’re time .