Group by and left join two data tables

Hi,

I have two data tables DT1 and DT2.

image

image

I have to compare the Code and Amount column between both tables, As well as the Tenor and PayT columns. Once both the columns are matched, I need to sum the Amount LC for the matching rows and get the output as below.

image

Please let me know how to achieve the same. Thank you.

@aishwarya1

Below is the output for your reference

Attached workflow

GroupAndSum.xaml (17.8 KB)

Hope this will help you

Please mark as solution if this helps

Thanks

1 Like

Hi,

How about the following?

dict = dt1.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Account").ToString,r("PayT").ToString)).ToDictionary(Function(g) g.Key,Function(g) g.Sum(Function(r) Int32.Parse(r("Amount LC").ToString)))

dt2 = dt2.AsEnumerable.Select(Function(r) dt2.Clone.LoadDataRow({r("Code").ToString,r("Tenor").ToString,if(dict.ContainsKey(Tuple.Create(r("Code").ToString,r("Tenor").ToString)),dict(Tuple.Create(r("Code").ToString,r("Tenor").ToString)),0)},False)).CopyToDataTable

Sample20220525-3.zip (9.5 KB)

Regards,

Could you please guide me how to assign the datatype for the dict variable?

Hi,

Can you check the following image?

First, choose Dictionary, then choose Tuple<T1,T2> as key.

Regards,

1 Like

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