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 :
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
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.
rikulsilva
Anil_G
Nguyen_Van_Luong1
lrtetala
pere
AJ_Ask
Anirudh_Tugawe
Gokul_Jayakumar
Shiva_Nikhil
Shreesh_Bhardwaj