do calculation from column data and fill the result in another column of DataTable

Question

User364607740 posted

I have a DataTable with columns,

ID   |  SUB1_TH  |  SUB1_PR  |  SUB2_TH  |  SUB2_PR  |  SUB1_TTL  |  SUB1_PER  |  SUB2_TTL  |  SUB2_TTL

I have to calculate the total of SUB1_TH, SUB1_PR and set into SUB1_TTL and calculate the percentage and fill in SUB1_PER. Similarly with the SUB2 also. 

 

It is only a sample table, my actual table contains around 50 columns and the row number is dynamic. How can I do this in DataTable?

Answer 1

User-943250815 posted

See if it works for you

For Each myDR As DataRow In myDataTable
  myDR("SUB1_TTL") = myDR("SUB1_TH") + myDR("SUB1_PR")
Next

Answer 2

User-893317190 posted

Hi scala_1988,

If your column is regular ,such as  SUB1_TH ,SUB2_TH, SUB3_TH... , you could  use for loop in a for loop.

Below is my code.

 If Not IsPostBack Then

            Dim dt As DataTable = New DataTable()
            dt.Columns.Add(New DataColumn("id", GetType(Int32)))
            dt.Columns.Add(New DataColumn("SUB1_TH", GetType(Int32)))
            dt.Columns.Add(New DataColumn("SUB1_PR", GetType(Int32)))
            dt.Columns.Add(New DataColumn("SUB1_TTL", GetType(Int32)))
            dt.Columns.Add(New DataColumn("SUB1_PER", GetType(Double)))
            dt.Columns.Add(New DataColumn("SUB2_TH", GetType(Int32)))
            dt.Columns.Add(New DataColumn("SUB2_PR", GetType(Int32)))
            dt.Columns.Add(New DataColumn("SUB2_TTL", GetType(Int32)))
            dt.Columns.Add(New DataColumn("SUB2_PER", GetType(Double)))
            dt.Rows.Add(1, 10, 20, 0, 0, 30, 40, 0, 0)
            dt.Rows.Add(2, 20, 30, 0, 0, 40, 50, 0, 0)
            dt.Rows.Add(3, 60, 70, 0, 0, 80, 90, 0, 0)
            Dim count = (dt.Columns.Count - 1) / 4   'get how many times you should cauculator, SUB_TH	SUB_PR	SUB_TTL	SUB_PER is one group exculde id , so divided by 4
            dt = dt.AsEnumerable().Select( ' the select method could get every row of the datatable and receive the mapped row finally the result will be collection of mapped row
             Function(row)
                 'here in the function you get every row, and set row columns you want 
                 For index = 1 To count   ' here total group is two , so loop for two times
                     ' set total 
                     row("SUB" & index & "_TTL") = DirectCast(row("SUB" & index & "_TH"), Int32) + DirectCast(row("SUB" & index & "_PR"), Int32)
                     'set percentage
                     row("SUB" & index & "_PER") = DirectCast(row("SUB" & index & "_PR"), Int32) * 1.0 / DirectCast(row("SUB" & index & "_TTL"), Int32)

                 Next
                 Return row
             End Function
             ).CopyToDataTable() 'the CopyToDataTable method convert the result to datatable

            GridView1.DataSource = dt
            GridView1.DataBind()


        End If

Here I use linq , but it also applies to jzero's for each.

You only need to write the same code in the body of for each.

Best regards,

Ackerly Xu