I have two DataTables:
In both of them is a column “Order Number”
I would like to take Each “Order Number” from dt1 and check if it exist in dt2
If exist-> take next “Order Numer”
If not exist → write comment in dt1 (Column “Comment’)” This order does not exist in dt2
How to do it?
January 26, 2023, 12:30pm
You can use For Each Row in Datatable in Dt1 and put into if condition to search Order Number in dt 2 and use this expression:
dt2.asEnumerable.Where(Function (x) x.item(“Order Number”).ToString.ToUpper.Trim.Equals(curretRow(“Order Number”).ToString.ToUpper.Trim)).Count = 0
Basically, get Order Number and lookup in dt2. If Order Number not found in any row in dt2, Count return = 0.
Then in True branch, put assign activity to currentRow.item(“Comment”) = “This order does not exist in dt2”
January 26, 2023, 2:08pm
FYI, another approach: using LINQ with Invoke code.
dt1.AsEnumerable.Where(Function(r) dt2.AsEnumerable.All(Function(r2) r2("Order Number").ToString<>r("Order Number").ToString)).ToList().ForEach(
r("Comment")="This order does not exist in dt2"
Sample20230126-1aL.zip (2.9 KB)
January 26, 2023, 2:20pm
Count return = 0.
Let us mention this recommendation as well:
So we rewrite to any with predicate
…Any(Function (x) …)
In case of another optimization is needed also have a look here:
Let us introduce the next changes
we are creating lists with the concatenated strings from dtold, dtnew
and creating a dictionary of only the items common in both lists
dtNewSet.asEnumerable.Select(Function (x) String.Join(“#”, arrColSet.Select(Function (k) x(k).toString.ToLower.Trim))).Distinct().toList
dtOldSet.asEnumerable.Select(Function (x) String.Join(“#”, arrColSet.Select(Function (k) x(k).toString.ToLower.Trim))).Distinct().toList
we took benefits from lists/dictionary keys
January 26, 2023, 2:29pm
Very thanks for advice.
I took notice and I will sharing in another oportunities