Excel duplicates and sum the amount

[ramshiva_reddy]

Hello All,
I have an excel file in which Output sheet “PolicyNo” matches in Sheet1 and Column “TypeOf” is Policy then add the amount from “Total_Premium” and write in Output sheet in “Inception_Premium” and if the column “TypeOf” is endorsement then add the amount from “Total_Premium” and write in Output sheet in “Tot_Endorsement_Premium”. How to do this? Please help me in this issue.
Corp_Filter.xlsx (457.3 KB)

[Anil_G (Anil Gorthi)]

@ramshiva_reddy

You can try this

use this in invoke code activity and send dt1 as in parameter which is the sheet1 and dt2 which is the outputdata as in/out parameters

dt2.AsEnumerable.ToList.ForEach(sub(r) r("Inception_Premium") = If(dt1.AsEnumerable.Where(function(x) x("PolicyNumber").ToString.Equals(r("PolicyNumber").ToString) and x("TypeOf").ToString.Equals("Policy")).Count>0,dt1.AsEnumerable.Where(function(x) x("PolicyNumber").ToString.Equals(r("PolicyNumber").ToString) and x("TypeOf").ToString.Equals("Policy")).Sum(function(x) Cdbl(x("Total_premium").ToString)).ToString,""))

dt2.AsEnumerable.ToList.ForEach(sub(r) r("Tot_Endorsement_Premium") = If(dt1.AsEnumerable.Where(function(x) x("PolicyNumber").ToString.Equals(r("PolicyNumber").ToString) and not x("TypeOf").ToString.Equals("Policy")).Count>0,dt1.AsEnumerable.Where(function(x) x("PolicyNumber").ToString.Equals(r("PolicyNumber").ToString) and not x("TypeOf").ToString.Equals("Policy")).Sum(function(x) Cdbl(x("Total_premium").ToString)).ToString,""))

cheers

[ramshiva_reddy]

Hello Bro,
Here I’m getting error as dt2 is not declared due to its protection level. How to resolve this issue.

[Anil_G (Anil Gorthi)]

@ramshiva_reddy

Dt2 is to be added as input /output argument in invoke code did you add?

Cheers

[ramshiva_reddy]

After passing dt2 as In/Out argument

[neha.upase]

You need to pass dt1 also

[ramshiva_reddy]

It’s still the same issue.

[ramshiva_reddy]

Can you please share me the sample workflow here.?

[Anil_G (Anil Gorthi)]

@ramshiva_reddy

please declare like this and pass the datatble variables

Sequence2.xaml (5.9 KB)

cheers