Adding two column values In DataTable

[marina.dutta]

Hi All,

I am reading an excel file and converting it to Data Table. I need to check if the Name in Column ‘C’ matches the I should take the sum of values in column ‘G’ .In my case it should be 8,000,000+296,000

Pleas let me know the logic

[Shiva_Nikhil]

@marina.dutta

you try with this

dt2.AsEnumerable.Sum(Function(x) cint(x(6).ToString))

dt2.AsEnumerable.Sum(Function(x) cdbl(x(6).ToString))

gives you the total sum

in place of 6 you can also pass your columnName which is INCLUDED UNITS

[Anil_G (Anil Gorthi)]

@marina.dutta

Please try below if you are looking for each name

Dt.AsEnumerable.Where(function(x) x("Name").ToString.Equals(NameVariable)).Sum(function(x) CDBL(x("Included units").ToString))

Use in assign output will be in double

If name might exist and some times its not then then first before sum use .Count>0 and on then side use sum else side it is 0 as nothing is found

Cheers

[Yoichi]

Hi,

How about the following sample?

dt.AsEnumerable.GroupBy(Function(r) r("NAME").ToString).Select(Function(g) dt.Clone.LoadDataRow(g.First().ItemArray.Take(5).Concat({g.Sum(Function(r) Double.Parse(r("INCLUDED UNITS").ToString,System.Globalization.NumberStyles.Any)),g.First.item(7)}).ToArray,False)).CopyToDataTable

Sample20231110-3L.zip (9.5 KB)

Regards,

[Sameer_Mahajan]

Hi @marina.dutta,
You can try this LinQ inside an Assign activity,

dt.AsEnumerable().Where(Function(x) (x(“Name”).Equals(yourVariable))).Sum(Function(y) CDbl(y(“INCLUDED UNITS”)))

Please mark this as the solution if it helps you.

Regards,

[marina.dutta]

@Yoichi

Just a small modification. I am checking by Internal ID’ column ‘Y’ .If that matches then I should sum the values.

[marina.dutta]

@Anil_G

small modification ,instead of name I am checking by column Y Internal ID. If that matches then I should sum the values.

[Yoichi]

HI,

Can you share input sample as file?

Regards,

[marina.dutta]

@Sameer_Mahajan

your variable here is the column name?

[marina.dutta]

0063f000004NCxfAAGDtExtractedReport.xlsx (9.7 KB)

[marina.dutta]

@Sameer_Mahajan

Something like this I need to write

dtExtractedTable_NS.AsEnumerable.Where(function(x) x(“InternalId”).ToString.Equals(CurrentRow(“INTERNAL ID”))).Sum(function(x) CDBL(x(“Included units”).ToString))

[marina.dutta]

@Anil_G
Is this correct?

dtExtractedTable_NS.AsEnumerable.Where(function(x) x(“InternalId”).ToString.Equals(CurrentRow(“INTERNAL ID”))).Sum(function(x) CDBL(x(“Included units”).ToString))

[Sameer_Mahajan]

what value do you want to compare the value in Internal Id against?

[Yoichi]

Hi,

Can you try the following sample?

dt.AsEnumerable.GroupBy(Function(r) r("INTERNAL ID").ToString).Select(Function(g) dt.Clone.LoadDataRow(g.First().ItemArray.Take(13).Concat({String.Format("{0:#,0}",g.Select(Function(r) if(Double.TryParse(r("INCLUDED UNITS").ToString,System.Globalization.NumberStyles.Any,System.Globalization.CultureInfo.InvariantCulture,New Double),Double.Parse(r("INCLUDED UNITS").ToString,System.Globalization.NumberStyles.Any),0)).Sum)}).Concat(g.First().ItemArray.Skip(14)).ToArray,False)).CopyToDataTable

Sample20231110-3L (2).zip (17.1 KB)

Regards,

[marina.dutta]

Thanks . Both the solution of your post and Anil worked. The below expression worked for me.

dtExtractedTable_NS.AsEnumerable.Where(function(x) x(“INTERNAL ID”).ToString.Equals(Row.Item(“INTERNAL ID”).ToString) ).Sum(function(x) CDBL(x(“Included units”).ToString))