Linq code to join and update value

Help Studio
, ,
1

image
image
Input dt1 and dt2

image
output dt3

2

Hi @sathya_giri

Try the below linq expression,

(From row1 In dt1.AsEnumerable()
                   Group Join row2 In dt2.AsEnumerable()
                   On row1("ID") Equals row2("ID") Into Group
                   From row2 In Group.DefaultIfEmpty()
                   Select dt1.Clone().LoadDataRow({row1("ID"), If(row2 IsNot Nothing, Math.Max(CInt(row1("Amount")), CInt(row2("Amount"))), CInt(row1("Amount")))}, False)).CopyToDataTable()

Change the column names as you required.

Hope it helps!!

3

Thankyou …but the point is we have to update amount column if the id is matched …not by using max function

4

Hi @sathya_giri

→ Read Range Workbook
image
Output-> dt1
→ Read Range Workbook
image
Output-> dt2
→ Use below query in Assign:

Assign -> dt_Result = (From row1 In dt1.AsEnumerable()
              Group Join row2 In dt2.AsEnumerable()
              On row1.Field(Of String)("ID") Equals row2.Field(Of String)("ID") Into Group
              From row2 In Group.DefaultIfEmpty()
              Let id = If(row1 IsNot Nothing AndAlso row2 IsNot Nothing, row1.Field(Of String)("ID"), If(row1 IsNot Nothing, row1.Field(Of String)("ID"), row2.Field(Of String)("ID")))
              Let amount = If(row1 IsNot Nothing AndAlso row2 IsNot Nothing, Math.Max(CInt(row1("Amount")), CInt(row2("Amount"))), If(row1 IsNot Nothing, CInt(row1("Amount")), CInt(row2("Amount"))))
              Select dt1.Clone().LoadDataRow({id, amount}, False)).Union(
              From row2 In dt2.AsEnumerable()
              Where Not dt1.AsEnumerable().Any(Function(r) r.Field(Of String)("ID") = row2.Field(Of String)("ID"))
              Select dt1.Clone().LoadDataRow({row2("ID"), row2("Amount")}, False)).OrderBy(Function(row) Integer.Parse(System.Text.RegularExpressions.Regex.Match(row.Field(Of String)("ID"), "\d+").Value)).CopyToDataTable()

dt_Result is of DataType System.Data.DataTable
→ Write Range Workbook dt_Result:
image

image
image890×643 35.4 KB

Hope it helps!!

5

thankyou parvathy …but my requirement is if id is matched the amount in dt2 is updated to amount in dt1

6

Hi @sathya_giri

Can you share the expected output.

Regards

7

dt1
image

dt 2
image

dt3-output
image

8

dt3 output
image

9

@Parvathy

this is my desired output previously you find max for amount column,i need to update amount from dt2 to dt1 if the id is matched

10

@sathya_giri

As per input in your output 11kl will have amount 6000 and 12uv will have amount 2000 right. Correct me if I’m wrong.

Regards

11

Hi @sathya_giri

→ Read Range Workbook
image
Output-> dt1
→ Read Range Workbook
image
Output-> dt2
→ Use below syntax in Assign:

dt_Result = (From row1 In dt1.AsEnumerable()
    Group Join row2 In dt2.AsEnumerable()
    On row1.Field(Of String)("ID") Equals row2.Field(Of String)("ID") Into Group
    From row2 In Group.DefaultIfEmpty()
    Let id = If(row1 IsNot Nothing AndAlso row2 IsNot Nothing, row1.Field(Of String)("ID"), If(row1 IsNot Nothing, row1.Field(Of String)("ID"), row2.Field(Of String)("ID")))
    Let amount = If(row2 IsNot Nothing, row2.Field(Of Double)("Amount"), row1.Field(Of Double)("Amount"))
    Select dt1.Clone().LoadDataRow({id, amount}, False)
).Union(
    From row2 In dt2.AsEnumerable()
    Where Not dt1.AsEnumerable().Any(Function(r) r.Field(Of String)("ID") = row2.Field(Of String)("ID"))
    Select dt1.Clone().LoadDataRow({row2("ID"), row2("Amount")}, False)
).OrderBy(Function(row) Integer.Parse(System.Text.RegularExpressions.Regex.Match(row.Field(Of String)("ID"), "\d+").Value)).CopyToDataTable()

dt_Result is of DataType System.Data.DataTable
→ Write Range Workbook dt_Result:
image

image
image890×755 39.4 KB

Hope it helps!!

12

Hey @sathya_giri

I used vb.net script.
Please check solution here:
BlankProcess106.zip (17.9 KB)

vb.net code:

Dim tempResult As DataTable = dtInput1.Clone()

Dim joinQuery = (From row1 In dtInput1.AsEnumerable()
                 Group Join row2 In dtInput2.AsEnumerable()
                 On row1.Field(Of String)("ID") Equals row2.Field(Of String)("ID")
                 Into row2Group = Group
                 From row2 In row2Group.DefaultIfEmpty()
                 Select If(row2 IsNot Nothing AndAlso row2Group.Count() > 0, row2, row1)).ToList()

For Each row In joinQuery
    tempResult.ImportRow(row)
Next

Dim onlyInDtInput2 = From row2 In dtInput2.AsEnumerable()
                     Where Not dtInput1.AsEnumerable().Any(Function(r) r.Field(Of String)("ID") = row2.Field(Of String)("ID"))
                     Select row2

For Each row In onlyInDtInput2
    tempResult.ImportRow(row)
Next

result = tempResulte

image
image532×567 16 KB

13

thankyou @Parvathy @pikorpa

1 Like
14

You’re welcome @sathya_giri

Happy Automation

15

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