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

• • Reply
  scala_1988

  Member

  35 Points

  144 Posts

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

  Feb 28, 2019 05:09 PM|scala_1988|LINK

  I have a DataTable with columns,

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

  <div>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. </div> <div> </div> <div>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?</div>
• • Reply
  jzero

  Member

  698 Points

  467 Posts

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

  Feb 28, 2019 11:47 PM|jzero|LINK

  See if it works for you

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

• • Reply
  Ackerly Xu

  Contributor

  3350 Points

  1285 Posts

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

  Mar 01, 2019 04:07 AM|Ackerly Xu|LINK

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.