Do a vlookup using LINQ and assign the value to the specified column

Hi,

The following is another approach to achieve it. Hope this helps you.

Code in InvokeCode

Dim dict As Dictionary(Of String,Double) =  AgeingDT.AsEnumerable.ToDictionary(Function(r) r("Invoice").ToString,Function(r) Double.Parse(r("Amount").ToString))
CollectionsDT = CollectionsDT.AsEnumerable.Where(Function(r)  dict.Keys.Contains(r("Invoice").ToString)).Select(Function(r) 
r("Sales Amount") = dict(r("Invoice").ToString)
Return r
End Function).CopyToDataTable()

Sample20211017-3.zip (4.8 KB)

Regards,

1 Like

Hello @Palaniyappan ,

thank you for the quick response.

Will this be an assign activity with dt as table name

or a invoke code where I need to pass collectionsDT (IN/OUT) and AgeingDT (IN) as arguments.

Maybe a sample work flow would be great.

Regards,
Manjesh

It will be in assign activity @manjesh_kumar

Dear @Palaniyappan ,

thank you very much it worked.

When I write to dt I am missing some column information which was there in collectionsDT.

The column name is “Inv Series”.

I would like to continue with the collectionsDT rather than writing to a new data table dt since I need to make many more calculations on the columns.

Thank you in advance for the great support.

Regards,
Manjesh

We can mention all the columns we want in the last select mentioned along the query

May be like this
Select CollectionsDT.clone.LoadDataRow(New Object(){
a(“Invoice”).tostring,
a(“Amount”).tostring,
b(“Sales Amount”).tostring,
a(“columnname_1”).ToString,
.
.
.
a(“columnname_N”).ToString},
False)).CopyToDataTable

Cheers @manjesh_kumar

Dear @Palaniyappan ,

Thank you that worked :slight_smile: as expected.

The above mentioned invoice numbers (screenshot highlighted) does not exist in AgeingDT and these records have disappeared after the assign activity. I need those records too if it does match since I have other computations for them. It would be a like an outer join rather than a inner join. All records from collectionsDT needs to appear even if there are no records in AgeingDT.

Regards,
Manjesh

1 Like

I believe, you need to find the common and uncommon rows on the basis of comparison of one column value in two datatable.

There are couple of ways to do that, below is the best and cleanest.

Dear @rahulsharma @Palaniyappan

Good Day.

I went through that post however I am still not clear. I would appreciate if you can use the LINQ query as stated by @Palaniyappan.

I want all “Invoice” from collectionsDT, whether there is a matching or no matching “Document Number” in AgeingDT.

AgeingDT
|Document Number|Amount|
|97139650|236736.76|
|97139652|69860|
|97139651|80930|
|97139648|157232.59|
|97139649|11295.86|
|97139527|270321.57|
|97139643|160850.12|
|97139644|26807.14|

collectionsDT

|Invoice|Amount|Sales Amount|Difference|Net Amount|TDS|Difference2|Profit Center|Comments|Comments1|Reason Code|Inv Series|
|8203000073|-201780|||||||||||
|8203000072|-1069080|||||||||||
|2100003040|-2361.6|||||||||||
|2100003039|-2361.6|||||||||||
|2100003038|-2361.6|||||||||||
|2100002952|-26981.86|||||||||||
|97139650|236736.76||||||||||SalesPayment|
|97139652|69860||||||||||SalesPayment|
|97139651|80930||||||||||SalesPayment|
|97139648|157232.59||||||||||SalesPayment|
|97139649|11295.86||||||||||SalesPayment|
|97139527|270321.57||||||||||SalesPayment|
|97139643|160850.12||||||||||SalesPayment|

collectionsDT after vlookup using LINQ (Expected Output)

Invoice Amount Sales Amount Difference Net Amount TDS Difference2 Profit Center Comments Comments1 Reason Code Inv Series
8203000073 -201780
8203000072 -1069080
2100003040 -2361.6
2100003039 -2361.6
2100003038 -2361.6
2100002952 -26981.86
97139650 236736.76 236736.76 SalesPayment
97139652 69860 69860 SalesPayment
97139651 80930 80930 SalesPayment
97139648 157232.59 157232.59 SalesPayment
97139649 11295.86 11295.86 SalesPayment
97139527 270321.57 270321.57 SalesPayment
97139643 160850.12 160850.12 SalesPayment
97139644 26807.14 26807.14 SalesPayment

Regards,
Manjesh

Fine then. We can try with JOIN DATATABLE activity

@manjesh_kumar

Hello @Palaniyappan ,

Good Day.

I saw a post which is very similar to my requirement.

I have changed LINQ accordingly however I am having error (screen shot) attached below.

(From a In collectionsDT.AsEnumerable()
Group Join b In AgeingDT.AsEnumerable On a(“Bank Invoice”) Equals b(“Document Number”) Into gj = Group
From g In gj.DefaultIfEmpty
Select ra = {a(“Bank Invoice”),If(isNothing(g), Nothing, g(1)) }
Select collectionsDT.clone.LoadDataRow(New Object(){
ra(“Reference”),
ra(“Bank Invoice”).tostring,
ra(“Bank Amount”).tostring,
math.Round(CDbl(g(“Ageing Amount”)),2),
ra(“Difference”),
ra(“Net Amount”),
ra(“TDS”),
ra(“Difference2”),
ra(“Profit Center”),
ra(“Comments”),
ra(“Comments1”),
ra(“Reason Code”)},
False)).CopyToDataTable()

Appreciate your valuable support as always.

Regards,
Manjesh

Just some inputs:
a faster technique with more compact code would be to construnct ra fully / partly with using take/skip on the a.itemArray

After Select ra - a is no longer in the scope that’s why you got this excpetion
for such scenario the LET keyword can help

Dear Peter,

Good Day.

An example would always help me understand better.

Regards,
Manjesh

give a try on following:

Assign Activity:
dtResult = collectionsDT.Clone

Assign activity:
dtResult =

(From a In collectionsDT.AsEnumerable()
Group Join b In AgeingDT.AsEnumerable 
On a(“Bank Invoice”) Equals b(“Document Number”) Into gj = Group
From g In gj.DefaultIfEmpty
Let slsa = If(isNothing(g), Nothing, g(1))
Let rapre = a.ItemArray.Take(2).Append(slsa)
Let rapost = a.ItemArray.Skip(3)
Let ra = rapre.Concat(rapost).toArray
Select r=dtResult.Rows.add(ra)).CopyToDataTable()

Dear Peter,

Thank you.

I was able to understand the code that is in BOLD, the rest did not go through my thick skin. Maybe a more detailed explanation would help me to understand better. My apologies since I have added an extra column “Reference” in comparison to my previous posts.

(From a In collectionsDT.AsEnumerable()
Group Join b In AgeingDT.AsEnumerable
On a(“Bank Invoice”) Equals b(“Document Number”) Into gj = Group
From g In gj.DefaultIfEmpty
Let slsa = If(isNothing(g), Nothing, g(1))
Let rapre = a.ItemArray.Take(2).Append(slsa)
Let rapost = a.ItemArray.Skip(3)
Let ra = rapre.Concat(rapost).toArray
Select r=dtResult.Rows.add(ra)).CopyToDataTable()

In dtResult the “Bank Amount” was replaced with “Amount” from AgeingDT where as it should have been in the “Sales Amount” column and retaining the “Bank Amount” from collectionsDT

(dtResult)

Reference Bank Invoice Bank Amount Sales Amount Difference Net Amount TDS Difference2 Profit Center Comments Comments1 Reason Code
4900067828 8203000073
4900067827 8203000072
4700334343 2100003040
4700334342 2100003039
4700334341 2100003038
4700332997 2100002952
4700305131 97139650 236921.86
4700305052 97139652 69914.62
4700305007 97139651 80993.28
4700305002 97139648 157355.52
4700304999 97139649 11304.69
4700304953 97139527 270532.92
4700304713 97139643 160975.88
4700304712 97139644 26828.1
4700304710 97139642 33914.07

Expected Output is

Reference Bank Invoice Bank Amount Sales Amount Difference Net Amount TDS Difference2 Profit Center Comments Comments1 Reason Code
4900067828 8203000073 -201780
4900067827 8203000072 -1069080
4700334343 2100003040 -2361.6
4700334342 2100003039 -2361.6
4700334341 2100003038 -2361.6
4700332997 2100002952 -26981.86
4700305131 97139650 236921.86
4700305052 97139652 69914.62
4700305007 97139651 80993.28
4700305002 97139648 157355.52
4700304999 97139649 11304.69
4700304953 97139527 270532.92
4700304713 97139643 160975.88
4700304712 97139644 26828.1
4700304710 97139642 33914.07

Appreciate the patience and support.

Regards,
Manjesh

better not change structures during a code integrations. This will affect the indexes. Better do it after a prototype is running successfully.

A datrow has an ItemArray which is an array of object holding the values from the columns

Let slsa = If(isNothing(g), Nothing, g(1))
an explicit check if the left join had a match and taking the value from second column (AgeingDt Amount)

Let rapre = a.ItemArray.Take(2).Append(slsa)
creating a part result by taking the first 2 items from itemarray and appending slsa on it

Let rapost = a.ItemArray.Skip(3)
taking all items from ItemArray ommiting the first 3 items

Let ra = rapre.Concat(rapost).toArray
constructing the final itemArray for the row which will be added
first 2 items + slsa (rapre) & rapost = Itemarray of new row from this loop


with the reference col on first position just edit the indexes / take / skip lengths and give a try on

(From a In collectionsDT.AsEnumerable()
Group Join b In AgeingDT.AsEnumerable
On a(“Bank Invoice”) Equals b(“Document Number”) Into gj = Group
From g In gj.DefaultIfEmpty
Let slsa = If(isNothing(g), Nothing, g(1))
Let rapre = a.ItemArray.Take(3).Append(slsa)
Let rapost = a.ItemArray.Skip(4)
Let ra = rapre.Concat(rapost).toArray
Select r=dtResult.Rows.add(ra)).CopyToDataTable()
1 Like

Dear Peter,

Good Day.

Thank you it is working as expected. :clap: :clap:

I have a question, how can I debug or check all that is inside this code (LINQ) for better understanding.

Thanks to @ppr @Palaniyappan @Yoichi @rahulsharma for all the great support as always.

Regards,
Manjesh

1 Like

Hello Peter,

The initial solution worked, however I tried the same on a different table in continuation to my previous LINQ, I am getting the following error.

Assign dtResult after LeftJoin with ZreturnDT: Collection was modified; enumeration operation might not execute.

ZreturnDT

INVOICE NO Profit Centre NETAMT .
90071230 51601-058 19.80
90071231 51601-058 8.38
90071232 51601-058 8.76
90071233 51601-058 2.24
91294051 51601-025 339,971.40
91294052 51601-025 300,064.50
91294053 51601-073 1,487,700.00
91294054 51601-025 339,971.40
91294055 51601-037 862,365.60
91294056 51601-037 670,771.20
91294057 51601-037 612,153.60
91294058 51601-037 2,583,878.40
91294059 51601-037 1,197,555.20
91294060 51601-037 1,724,731.20
91294061 51601-037 862,365.60
91294062 51601-001 789,609.60
91294063 51601-031 409,909.20
91294064 51601-031 272,230.80

(From a In dtResult.AsEnumerable()
Group Join b In ZreturnDT.AsEnumerable()
On a(“Bank Invoice”) Equals b(“INVOICE NO”) Into gj = Group
From g In gj.DefaultIfEmpty
Let slsa = If(isNothing(g), Nothing, g(2))
Let rapre = a.ItemArray.Take(5).Append(slsa)
Let rapost = a.ItemArray.Skip(6)
Let ra = rapre.Concat(rapost).toArray
Select r=dtResult.Rows.add(ra)).CopyToDataTable()

What have I done wrong here ???

Regards,
Manjeesh

extract parts of it and use e.g. immediate panel:

let us keep the topic strict to one case to get it solved straighfroward. This helps researches when they are looking for similar things.

you cannot add rows to dtResult while looping (yoe are doing this by From a in dtResult…)

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.