Summing the quantity column

Hi guys, what I need to do is to sum the numbers for the month of July together, and at the same time also multiplying the numbers by 10 ( because its currently in bottles of 10L and I want to calculate the total liters)

Currently:
image

Expected outcome
image

From my research I believe I need to use LINQ somehow but I am not too sure.

Also the variables I used to get the current result above is the “Month” variable and “AmtofBTLs” variable

1 Like

dt_Input.AsEnumerable.GroupBy(Function(row) row(“Month of Bill”).ToString).Select(Function(row) dt_Output.Rows.Add(row.First.Item(“Month of Bill”).ToString,(row.Sum(Function(val) Cint(val(“Bottles of 10L”))*10)))).CopyToDatatable

1 Like

@HENG_JUN_YING_AARON

Assign totalLiters = dtData.AsEnumerable() _
.Where(Function(row) DateTime.ParseExact(row(“Date”).ToString, “MMM-dd”, System.Globalization.CultureInfo.InvariantCulture).Month = 7) _
.Sum(Function(row) Convert.ToInt32(row(“Quantity”)) * 10)

Hi @HENG_JUN_YING_AARON

Use the below Linq queries

OutputDT = DT.Clone
OutputDT = DT.AsEnumerable.GroupBy(Function(x)x("Month of Bill").ToString).Select(Function(x)OutputDT.Rows.Add(x.First.Item("Month of Bill").ToString,x.Sum(Function(d)CDbl(d("Bottles of 10L").ToString)*10))).CopyToDatatable

I have developed a xaml file. Refer the below image

Result check below

I am uploading the xaml here for your reference.
Expressions_Practice.xaml (9.3 KB)

Hope it helps!!

Hi,

Can you try the following sample?

dt.AsEnumerable.GroupBy(Function(r) r("Month of bill").ToString).Select(Function(g) dt.Clone.LoadDataRow({g.Key,g.Sum(Function(r) CInt(r("Bottles of 10L").ToString)*10)},False)).CopyToDataTable()

Sample20230720-5L.zip (8.3 KB)

Regards,

Hi @HENG_JUN_YING_AARON

  1. Create a Output Datatable, say OutputDT having the same 2 columns, so we clone the datatable using an Assign activity.
OutputDT = DT.Clone
  1. Next, we can perform the Group By and Sum operation on the Datatable to get the resultant datatable.
OutputDT =DT.AsEnumerable.GroupBy(Function(x)x("Month of Bill").ToString).Select(Function(x)outputDT.Rows.Add(x.First.Item("Month of Bill").toString,x.Sum(Function(d)CDbl(d("Bottles of 10L").ToString)*10))).CopytoDataTable

Hope it helps!!
Regards,

1 Like

hi @HENG_JUN_YING_AARON
DT2=DT1.Clone
DT2=dt1.AsEnumerable.GroupBy(Function(x)x(“Month of Bill”).ToString).Select(Function(x)dt2.Rows.Add(x.First.Item(“Month of Bill”).ToString,x.Sum(Function(d)CDbl(d(“Bottles of 10L”).ToString)*10))).CopyToDatatable

note: dt1 is your first dataTable

Hi @Yoichi and @mkankatala both of you have solved my problem, thank you so much to both of you!

Regards

1 Like

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