Check value in column to another column in other datatable

Help Activities
,
1

if the value in column ‘Name’ & column ‘Age’ Data1 is same with column ‘Name’ & column ‘Age’ Data2, keep value the last row of Data2

Data1:
Name Age Color
Duck 1 Red
Dog 2 White
Duck 4 Red

Data2:
Name Age Color
Duck 1 Red
Duck 1 Green
Fish 3 Gold
Duck 5 Yellow

Expected:
Name Age Color
Duck 1 Green
Dog 2 White
Fish 3 Gold
Duck 4 Red
Duck 5 Yellow

anyone can help me?

Regards,
brian

2

@Brian_Henokh1

-Assign activity:

dtMerged = (From row1 In dt1.AsEnumerable()
Group Join row2 In dt2.AsEnumerable()
On row1.Field(Of String)(“Name”) Equals row2.Field(Of String)(“Name”) And
row1.Field(Of Int32)(“Age”) = row2.Field(Of Int32)(“Age”)
Into Group = Group
From row2 In Group.DefaultIfEmpty()
Select If(row2 Is Nothing, row1, row2)).CopyToDataTable()

-Assign activity:

dtResult = (From row2 In dt2.AsEnumerable()
Where Not dtMerged.AsEnumerable().Any(Function(row) row2(“Name”).ToString() = row(“Name”).ToString() And
row2(“Age”).Equals(row(“Age”)))
Select row2).CopyToDataTable()

-Output Data Table

3

Hi,

Can you try the following sample?

image
image795×751 24.8 KB

dt2 = dt2.AsEnumerable.Concat(dt1.AsEnumerable).GroupBy(Function(r) Tuple.Create(r("Name").ToString,r("Age").ToString)).OrderBy(Function(g) CInt(g.Key.Item2)).Select(Function(g) dt2.Clone.LoadDataRow({g.Key.Item1,g.Key.item2,g.Last().Item("Color").ToString},False)).CopyToDataTAble()

Sample20231212-2L.zip (2.9 KB)

Regards,

4

hello, thankyou for your response but the expected result still not correct

the output after I tried is

Name,Age,Color
Fish,3,Gold
Duck,5,Yellow

5

Hi @Brian_Henokh1

Can you try the below code

result = (From row In data1.AsEnumerable() Select row).Concat(From row In data2.AsEnumerable() Select row).GroupBy(Function(r) New With {.Name = r.Field(Of String)("Name"), .Age = r.Field(Of Integer)("Age"), .Color = r.Field(Of String)("Color")}).Select(Function(g) g.First()).CopyToDataTable()

6

hello, thankyou for your response but the expected result still isn’t correct

the output after I tried is

Name,Age,Color
Duck,1,Red
Dog,2,White
Fish,3,Gold
Duck,4,Red
Duck,5,Yellow

8

hello, thankyou for your response but the expected result still isn’t correct

the output after I tried is

Name,Age,Color
Duck,1,Red
Dog,2,White
Duck,4,Red
Duck,1,Red
Duck,1,Green
Fish,3,Gold
Duck,5,Yellow

9

Hi,

Sorry, I had a mistake. How about the following sample?

dt1.AsEnumerable.Concat(dt2.AsEnumerable).GroupBy(Function(r) Tuple.Create(r("Name").ToString,r("Age").ToString)).OrderBy(Function(g) CInt(g.Key.Item2)).Select(Function(g) dt2.Clone.LoadDataRow({g.Key.Item1,g.Key.item2,g.Last().Item("Color").ToString},False)).CopyToDataTAble()

Sample20231212-2L (2).zip (2.9 KB)

image

Regards,

1 Like
10

I see, thankyou very much for your help & your attention. :100:

regards,
brian

1 Like
11

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