Linq query for comparing 2 datatables

Help Activities
, ,
1

Hi Community,

I want to compare 2 excel sheets where I take invoice number from first excel sheet and search for a match in second excel sheet, if invoice match is found then respective amount should be compared in both excel sheets. If the amount is same then update status as “YES” and If the amount is not same then update status as “NO” in status column of first excel sheet.
please provide me a linq query for this.

Thanks

2

Hi @praveena.nadi
Can you try this

I hope it helps!!

3

Hi,

I did not understand as I am new to Linq, could you please send me a detailed query with respect to my requirement

Thanks in advance

4

@Anil_G, @raja.arslankhan, @supermanPunch

By any chance, you guys can help me with this please

Thanks

5

@praveena.nadi

You can use join activity instead and perform a left join on the main table…that way matched records and non matched records can be identified…if record is not matched as you are doing left join in the output table columns related to second table will be empty and you can give status as no and for remaining give status as yes and then use filter datatable to remove the column which you dont need

Or add a new column for result(yes/no) and use something like this in invoke code

dt.AsEnumerable.ToList.ForEach(Sub(r) r("result") = If(dt2.AsEnumerable.Where(function(x) x("ID").ToString.Equals(r("ID").ToString) AndAlso x("Amount").ToString.Equals(r("Amount").ToString)).Count>0,"Yes","No"))

cheers

6

[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

As it is also an Datacolumn update case we recommend
How to Update Data Column Values of a Data Table | Community Blog

For the match / not match calculation a left join is reommended OR combining a LINQ along with a for each row

7

As an alternate strategy the match / non match case can be modelled as a Lookup case

DictLK | Dictionary(Of String, String) =

dt2.AsEnumerable().toDictionary(Function (x) x(JoinColNameOrIndex).toString.Trim, Function (x) x(ValueColNameOrIndex).toString.Trim)

Then we do the update by:

For each row in Datatable Activity | row in dt1

  • Assign: strKey = row(JoinColNameOrIndex).toString.Trim
  • Assign: strVal = row(ValueColNameOrIndex).toString.Trim
  • IF: DictLK.ContainsKey(strKey) AndAlso (strVal.Equals(DictLK(strKey)))
    • Then: row(StatusColNameOrIndex) = “YES”
    • Else: row(StatusColNameOrIndex) = “NO”
8

@praveena.nadi
Try this one:

Assign activity:
    Left side: updatedSheet1Data (List<Row>)
    Right side:
        (From row1 In sheet1.AsEnumerable()
         Let matchingRow = sheet2.AsEnumerable().FirstOrDefault(Function(row2) row2("InvoiceNumber").ToString() = row1("InvoiceNumber").ToString())
         Let amount1 = If(matchingRow IsNot Nothing, Decimal.Parse(matchingRow("Amount").ToString()), 0)
         Let amount2 = Decimal.Parse(row1("Amount").ToString())
         Let status = If(amount1 = amount2, "YES", "NO")
         Select row1.Field(Of DataRow)("Status") = status).ToList()
9

@raja.arslankhan
I tried with your code, its showing as implicit conversion from string to integer as an error.
Unable to figure it out. please help

thanks