How to Look Up in DT, with Contains instead of Equal?

[benjiherb]

The use case is as follows:

There is a value, and part of this value will exist in ColumnA of datatable. Without doing a For Each, I would like an easy way to find the value of NewVariable where, if LookupValue contains Column A, NewVariable=ColumnB of matching index.
Since LookupValue can theoretically contain multiple rows of ColumnA, I would want the lowest index solution.

Please see example below.

LookupValue: ABCD

Datatable Values:
ColumnA,ColumnB
AB,11
ZY,22
VB,33
KL,44
CD,55

Expected output of NewVariable=11
(CD is in a higher row, so 55 would not be the expected output)

[Anil_G (Anil Gorthi)]

@benjiherb

Please try this linq query

Dt.AsEnumerable.Where(function(x) lookupvalue.Contains(x("ColumnA").ToString)).Select(function(x) x("ColumnB”).ToString)(0)

Here we are trying to get the first macthed value with the given lookupvalue

Cheers

[arjunshenoy]

Hi @benjiherb

Please try the following logic in the Invoke Code & let us know:

LookupValue = "ABCD"
matchingRows = DataTable.Select("ColumnA = '" + LookupValue + "'")
if matchingRows.Count > 0:
    matchingRows = matchingRows.OrderBy(Function(row) row("ColumnB")).ToArray()
    NewVariable = matchingRows(0)("ColumnB").ToString()

Hope this helps,
Best Regards.

[benjiherb]

Thank you!!!