VLOOKUP in 2 data tables

[Sathish_Kumar_S]

DataTable 1 :

DataTable 2 ( VIP User List)

How to do VLOOKUP in 2 data tables?

We need look for VIP user in the datatable2, if the user present in datatable1 is available in Datatable2 then update the vip user name in “C” column in datatable1 else update as “N/A”?

[Umadevi_Sanjeevi]

Hi @Sathish_Kumar_S

DT1 = Datatable 1
DT2 = Datatable 2

1.Use For each row for DT1
2.Inside For each row use “Update Row item” activity
Value : If(DT2.Select(“[VIP User List]='”+CurrentRowDT1(“User”).ToString.Trim+“'”).Count>0,
CurrentRowDT1(“User”).ToString,
“NA”)
Row : CurrentRowDT1
Column : Select “Name”. Value of the column name will be “VIP user”

Thanks!

[Sathish_Kumar_S]

@Umadevi_Sanjeevi

Select “Name”. Value of the column name will be “VIP user”

@Umadevi_Sanjeevi Thank you for your quick response.

Getting attached error … The VIP1 is user name not the column

[ppr (Peter)]

As an alternate, we can do the following: HashSet LookUp Approach

Vars:

Flow:

myLookUpHashSet =
new HashSet(Of String) (dt2.AsEnumerable.Select(Function (x) x("Column1").toString.Trim))
dt2.AsEnumerable.Select(Function (x) x("Column1").toString.Trim).ToHashSet()

row(“isMatch”) =
If(myLookUpHashSet.Contains(row("Column1").toString.Trim), row("Column1"),"NA")

Traces:

After update:

We evolved from LKDictionary to HashSet as we avoided the unneeded values from the keyValuePairs

LINQ query running for long hours

Let us introduce the next changes [grafik] 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 ListDT1.Intersect(ListDT2).ToDictionary(Fun…

[Umadevi_Sanjeevi]

Hi @Sathish_Kumar_S ,

Have got output . pls refer this xaml

DT1

DT2:

Output:

User	Type	VIP user
AABS	Dialog	AABS
BBSA	Dialog	BBSA
BBSAAAA	Dialog	NA

Test.xaml (13.3 KB)

Thanks!