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")
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 NextPivot 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 IfThe error falls on the following line:
dest.Rows.Find(r("name")).Item(r("store")) = r("NPG")
Any help would be much appreciated
Thanks.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Pivot DataTable problems
Nov 08, 2012 12:43 AM|LINK
What detailled information of error have you got now? Can you tell us?