Pivot DataTable problems RSS

• • Reply
  

  rankone

  Member

  53 Points

  53 Posts

  Pivot DataTable problems

  Nov 06, 2012 11:21 PM|LINK

  Hey Everyone!

  So my issue is I am trying to pivot a dataset within the application in order for it to be displayed in a certain format. In my dataset (from MS SQL) I am ordering it by store, so I have noticed when a User has only 1 store this code works, but when the user has multiple stores the pivoting doesn't quite work. The error I get is that the 2nd store doesn't belong in the PivotTable. The reason for pivoting the data is so that I can group the categories & have price points vary by stores going horizontally. This works for one store but not more than that, any ideas what may be happening?

  Here's my code:
  (Page Load)

  Dim ds As DataSet = DataHandler.GetSystem(Session("LNumber"), Session("ID"))
  
  'Pivot data
  Dim pivotedData As DataTable = DataHandler.PivotDataTable(ds.Tables(0))
  
  
  'count how many stores there are for this report
  Dim distinctStores As Integer = DataHandler.SelectDistinct(ds.Tables(0).TableName, ds.Tables(0), "store").Rows.Count
  
  'how many stores to show per table
  Const numColumns As Integer = 5
  
  'determine how many tables to create
  Dim numTables As Integer = Math.Floor(distinctStores / numColumns)
  If distinctStores Mod numColumns > 0 Then
  numTables += 1
  End If
  
  Dim storeCounter As Integer = 0
  
  For i As Integer = 0 To numTables - 1
  Dim dt As New DataTable("GridTable")
  dt.Columns.Add(New DataColumn(pivotedData.Columns(0).ColumnName, pivotedData.Columns(0).DataType))
  dt.Columns.Add(New DataColumn(pivotedData.Columns(1).ColumnName, pivotedData.Columns(1).DataType))
  dt.Columns.Add(New DataColumn(pivotedData.Columns(2).ColumnName, pivotedData.Columns(2).DataType))
  
  
  For i2 As Integer = 0 To numColumns * 3 - 1 Step 3
  If storeCounter < distinctStores Then
  dt.Columns.Add(New DataColumn(pivotedData.Columns(numColumns * i * 3 + (3 + i2)).ColumnName, System.Type.GetType("System.Double"))) 'pivotedData.Columns(numColumns * i * 2 + (2 + i2)).DataType))
  dt.Columns.Add(New DataColumn(pivotedData.Columns(numColumns * i * 3 + (3 + i2 + 1)).ColumnName, pivotedData.Columns(numColumns * i * 2 + (2 + i2 + 1)).DataType))
  dt.Columns.Add(New DataColumn(pivotedData.Columns(numColumns * i * 3 + (3 + i2 + 2)).ColumnName, pivotedData.Columns(numColumns * i * 2 + (2 + i2 + 2)).DataType))
  storeCounter += 1
  End If
  Next

  
  
  

  Pivot Table Code:

  Public Shared Function PivotDataTable(ByVal source As DataTable) As DataTable
  Dim dest As New DataTable("Pivot" + source.TableName)
  '1st column is for the item descriptions 
  dest.Columns.Add("Name")
  '2nd column is for category
  dest.Columns.Add("Category")
  'the remaining columns 
  'from each source table row (1st column)
  dest.Columns.Add("ItemNum")
  dest.PrimaryKey = New DataColumn() {dest.Columns("Name")}
  
  'Iterate through a collection adding a column
  'for each store and a column for each color
  For Each r As DataRow In source.Rows
  If Not dest.Columns.Contains(r("store").ToString + "(Current)") Then
  
  dest.Columns.Add(r("store").ToString + "(Current)")
  dest.Columns.Add(r("store").ToString + "(New)")
  dest.Columns.Add(r("store").ToString + "_color")
  End If
  Next
  
  For Each r As DataRow In source.Rows
  If Not dest.Rows.Contains(r("name").ToString) Then
  Dim colIndex As Integer = dest.Columns.IndexOf(r("store") + "(Current)")
  
  
  Select Case colIndex
  Case 2
  
  dest.Rows.Add(r("name").ToString, r("cat").ToString, r("VP").ToString, r("NPG").ToString)
  
  Case 3
  
  dest.Rows.Add(r("name").ToString, r("cat").ToString, r("item").ToString, r("VP").ToString, r("NPG").ToString)
  Case 4
  
  dest.Rows.Add(r("name").ToString, r("cat").ToString, r("item").ToString, Nothing, Nothing, r("VP").ToString, r("NPG").ToString)
  Case 5
  
  dest.Rows.Add(r("name").ToString, r("cat").ToString, r("item").ToString, Nothing, Nothing, Nothing, r("VP").ToString, r("NPG").ToString)
  Case 6
  
  dest.Rows.Add(r("name").ToString, r("cat").ToString, r("item").ToString, Nothing, Nothing, Nothing, Nothing, r("VP").ToString, r("NPG").ToString)
  Case 7
  
  dest.Rows.Add(r("name").ToString, r("cat").ToString, r("item").ToString, Nothing, Nothing, Nothing, Nothing, Nothing, r("VP").ToString, r("NPG").ToString)
  Case 8
  
  dest.Rows.Add(r("name").ToString, r("cat").ToString, r("item").ToString, Nothing, Nothing, Nothing, Nothing, Nothing, r("VP").ToString, r("NPG").ToString)
  Case Else
  
  dest.Rows.Add(r("name").ToString, r("cat").ToString)
  End Select
  
  dest.Rows.Find(r("name")).Item(r("store") + "_color") = r("color")
  Else
  dest.Rows.Find(r("name")).Item(r("store")) = r("NPG")
  dest.Rows.Find(r("name")).Item(r("store") + "_color") = r("color")
  End If
  

  
  
  

  The error falls on the following line:
  dest.Rows.Find(r("name")).Item(r("store")) = r("NPG")

  Any help would be much appreciated
  Thanks.

  • Edited by rankone on Nov 06, 2012 11:22 PM
  • Moved by tmorton on Nov 07, 2012 01:34 PM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Pivot DataTable problems

  Nov 08, 2012 12:43 AM|LINK

  rankone

  dest.Rows.Find(r("name")).Item(r("store")) = r("NPG")

  What detailled information of error have you got now? Can you tell us?