How to sum three columns in LINQ query

i have three columns and i need to sum three columns and want the result in data table so i can write range so how to do this by using linq query sample attachments added

image

@sathish_Kumar6

Assign activity:
resultDataTable = (From row In yourDataTable.AsEnumerable()
Let sum = row.Field(Of Double)(“Column1”) + row.Field(Of Double)(“Column2”) + row.Field(Of Double)(“Column3”)
Select yourDataTable.Clone().Rows.Add(sum)).CopyToDataTable()

Hi @sathish_Kumar6

→ Use read range workbook activity to read the excel and store in a datatable. Ex → dtInput
→ Take an assign activity to write linq expression

- Assign -> dtOutput = dtInput.AsEnumerable()
    .Select(row => new
    {
        Column1 = row.Field<int>("Column1"),
        Column2 = row.Field<int>("Column2"),
        Column3 = row.Field<int>("Column3"),
        Final = row.Field<int>("Final")
    })
    .Select((row, index) => dtOutput.Rows[index]["Final"] = row.Column1 + row.Column2 + row.Column3)
    .CopyToDataTable();

→ Use write range workbook activity to write the dtoutput to excel

Note : In the Column1, Column2, Column3 change it as your column names.

Hope it helps!!

Hi @sathish_Kumar6

=> Use Read Range Workbook to read the excel and store it in an datatable.
=> Use the below query in Assign activity:

dt= (From row In dt.AsEnumerable()
                   Select newRow = dt.Clone().LoadDataRow({row("Material1"), row("Material2"), row("Material3"), row.Field(Of Double)("Material1") + row.Field(Of Double)("Material2") + row.Field(Of Double)("Material3")}, False)).CopyToDataTable()

=> Use Write Range Workbook to write the data back to excel
Input:


Output:

workflow:

xaml:
Sequence5.xaml (7.5 KB)

Hope it helps!!
Regards

Hi @sathish_Kumar6

Hope it helps!!

Getting error like this

Assign: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.

im getting the below error

@sathish_Kumar6

Remove the double quotes and try to give the double quotes

can you write sample

@sathish_Kumar6

(From row In DT1.AsEnumerable()
Let sum = Convert.ToDouble(row(“Hi”)) + Convert.ToDouble(row(“Hello”)) + Convert.ToDouble(row(“He”))
Select DT1.Clone().Rows.Add(row(“Hi”), row(“Hello”), row(“He”), sum)).CopyToDataTable()

Hi @sathish_Kumar6

Did you try this:

=> Use Read Range Workbook to read the excel and store it in an datatable.
=> Use the below query in Assign activity:

dt= (From row In dt.AsEnumerable()
                   Select newRow = dt.Clone().LoadDataRow({row("Material1"), row("Material2"), row("Material3"), row.Field(Of Double)("Material1") + row.Field(Of Double)("Material2") + row.Field(Of Double)("Material3")}, False)).CopyToDataTable()

=> Use Write Range Workbook to write the data back to excel
Input:


Output:

workflow:

xaml:
Sequence5.xaml (7.5 KB)

Hope it helps!!
Regards

Thanks for the effort reply but im getting error like this

Also i forgot to mention i have numbers like float so that would make difference?

Hi @sathish_Kumar6

Try this once:

dt= (From row In dt.AsEnumerable()
                   Select newRow = dt.Clone().LoadDataRow({row("Material1"), row("Material2"), row("Material3"), row.Field(Of String)("Material1") + row.Field(Of String)("Material2") + row.Field(Of String)("Material3")}, False)).CopyToDataTable()

Regards

im getting output like this but these datas are not present in the input file also i need only last column so could you help on this

sorry i dont have latest excel… also i want to achieve this without using excel so thats y im looking for linq query anyway really thanks for the reply

1 Like

Hi @sathish_Kumar6
=> Use Read Range Workbook to read the exce3l and store it in an datatable say dt
Input:


=>Use the below linq query in assign acitivity:

dt= (From row In dt.AsEnumerable()
                     Let sum = row.Field(Of Double)(0) + row.Field(Of Double)(1) + row.Field(Of Double)(2)
                     Select newRow = dt.Clone().LoadDataRow({row(0), row(1), row(2), sum}, False)).CopyToDataTable()

=> Use Write Range workbook to write the data back to excel.
Output:


Workflow:

xaml:
Sequence5.xaml (7.5 KB)

Hope it helps!!
Regards

1 Like

hey thanks it works is there anyway i could get only final column alone like i need only sum column alone

1 Like

Hi @sathish_Kumar6

If you find the solution for your query please mark it as solution to close the loop.

Happy Automation

Regards

ok thanks for the support

Hi @sathish_Kumar6

You can use the below code in Invoke Code activity:

out_dt = New DataTable
out_dt.Columns.Add("Final", GetType(Double))

For Each row In dt.AsEnumerable()
    Dim sum = row.Field(Of Double)("Material1") + row.Field(Of Double)("Material2") + row.Field(Of Double)("Material3")
    Dim newRow = out_dt.NewRow()
    newRow("Final") = sum
    out_dt.Rows.Add(newRow)
Next

Below are the Invoked arguments:


Workflow:

Output:

Regards

1 Like