Comparison of two tables using Linq

 There are two tables

Table-1

CODE AMOUNT
A1 200
A2 400
A3 300
A4 500
A5 600
A6 700
A7 800
A8 900
A9 100
A10 500


Table-2

 

CODE AMOUNT
A1 200
A3 300
A6 350
A7 800
A8 1800
A9 100
A10 500
A2 400
A11 10
A12 200
A13 300
A10 400


Need a table like this

 

CODE AMOUNT DIFFRENCE
A1 200 0
A3 300 0
A6 700 350
A7 800 0
A8 900 -900
A9 100 0
A10 500 0
A2 400 0
A11 #N/A #N/A
A12 #N/A #N/A
A13 #N/A #N/A
A10 500 100


The third table contains all the Codes of table-2 and the amount from table-1 wrt to code(which are present in table-1) and the diff col (table-1_amount  -   table-3_amount). Normally it is a VLOOKUP. (table-3 contains the amount of all those codes which are present in table-1 and table-2)

Need to perform this using LINQ.

@kumar.varun2

give a first try on join datatable activity - join type outer join

fetching the evaluated result from joined table result we would do with e.g. LINQ

In one of your solution on the forum, I found the linq query and made some changes. It is working fine now.

(From d In dtData2.AsEnumerable
Let check = dtData1.AsEnumerable.Where(Function ® r(0).ToString.Trim.Equals(d(0).ToString.Trim)).ToList
Let c2 = If(check.Count()>0, check(0)(1), “”)
Let c3 = If(Not c2.Equals(""), (Convert.ToDouble(d(1).ToString)-Convert.ToDouble(c2.ToString)).ToString, “NA”)
Let ra = {d(0), c2, c3}
Select dtResult.Rows.Add(ra)).CopytoDataTable

1 Like

@kumar.varun2
the case has a chance to get turned from full outer join to left join (dt2 - to - dt1)

I will have a look on it later. In the meanwhile find a demo on left join done with LINQ here:

1 Like

@kumar.varun2

find the alternate approach implementing a left join (fro dt2-to-dt1)


grafik

maybe it it will give better results when:

  • multiple same codes from dt2 will be present in dt1
  • AND the result will be deduplicated

find starter help here:
LeftJoin_1Col_DiffReport.xaml (12.1 KB)

Please explain the query.

Could you please explain the working of ‘group’?

@kumar.varun2
have a look here:

okay. Thanks

While using the above query on 6,00,000 rows of data, it is taking a lot of time. Can we decrease time somehow?

it is a very relative mesurement. Lets try to bring it into facts and numbers.
However lets find out which building block is consuming which portion of execution time. From where is the data retrieved, is it Excel?

The query part is taking time.

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