I have a query where I am doing a vlookup using LINQ however I not able to achieve the last step to output the data into the data table. Please correct where I am going wrong
I need the Amount column from AgeingDT to Sales Amount column in CollectionsDT
if the Invoice matches the Document Number
(From a In collectionsDT.AsEnumerable()
Join b In AgeingDT.AsEnumerable() On convert.todouble(a(“Invoice”)) Equals convert.todouble(b(“Document Number”)) select a(“Sales Amount”)=b(“Amount”))
Hope the below expression would help you resolve this
dt = (From a in CollectionsDT.AsEnumerable() Join b in AgeingDT.AsEnumerable() on Convert.ToDouble(a(“Invoice”).ToString) Equals Convert.ToDouble(b(“Document Number”).ToString)
Select CollectionsDT.clone.LoadDataRow(New Object(){
a(“Invoice”).tostring,
a(“Amount”).tostring,
b(“Sales Amount”).tostring},
False)).CopyToDataTable()
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
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.
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()
(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()
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
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()
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()