How to pivot and unpivot using

Posted by Rajnilari2015 under VB.NET category on | Points: 40 | Views : 301
Let's say we have the below RecordSet
Product Y1 Y2 
Product1 1 2
Product2 2 3

The processed output should look as under
YKey = Y1  Year = 2016 Product1 = 1 Product2 = 2
YKey = Y2 Year = 2017 Product1 = 2 Product2 = 3

The below program will help us to do so

Imports System.Collections.Generic
Namespace ConsoleApplication3
Class Program
Private Shared Sub Main(args As String())
'unpivot records
Dim unpivotRecords = New With { _
Key .Product1 = Unpivot(0), _
Key .Product2 = Unpivot(1), _
'pivot records
Dim pivotRecords = Pivot(unpivotRecords)
'display the records
pivotRecords.ForEach(Function(i) Console.WriteLine("YKey = {0} Year = {1} Product1 = {2} Product2 = {3}", i.YKey, i.Year, i.Product1, i.Product2))
End Sub
#Region "Methods"
'Source Records
Private Shared Function GetSourceRecords() As List(Of InputData)
Dim source As New List(Of InputData)()
source.Add(New InputData() With { _
Key .ProductName = "Product1", _
Key .Year1 = 1, _
Key .Year2 = 2, _
source.Add(New InputData() With { _
Key .ProductName = "Product2", _
Key .Year1 = 2, _
Key .Year2 = 3, _
Return source
End Function
'Unpivot records
Private Shared Function Unpivot(row As Integer) As Dictionary(Of String, List(Of Integer))
Dim source = GetSourceRecords()
Dim yearwiseProductValues As New List(Of Integer)()
Dim multiKeyDictionary = New Dictionary(Of String, List(Of Integer))()
multiKeyDictionary.Add(source(row).ProductName, yearwiseProductValues)
Return multiKeyDictionary
End Function
'Pivot records
Private Shared Function Pivot(unpivotRecords As dynamic) As List(Of OutputData)
Dim count As Integer = 10
Dim startYear As Integer = 2016
Dim pivotRecords = New List(Of OutputData)()
For i As Integer = 0 To count - 1
pivotRecords.Add(New OutputData() With { _
Key .YKey = "Y" + (i + 1), _
Key .Year = startYear + i, _
Key .Product1 = unpivotRecords.Product1("Product1")(i), _
Key .Product2 = unpivotRecords.Product2("Product2")(i), _
Return pivotRecords
End Function
#End Region
End Class
#Region "Data Structures"
Friend Class OutputData
Public Property YKey() As String
Return m_YKey
End Get
Friend Set
m_YKey = Value
End Set
End Property
Private m_YKey As String
Public Property Year() As Integer
Return m_Year
End Get
Friend Set
m_Year = Value
End Set
End Property
Private m_Year As Integer
Public Property Product1() As Integer
Return m_Product1
End Get
Friend Set
m_Product1 = Value
End Set
End Property
Private m_Product1 As Integer
Public Property Product2() As Integer
Return m_Product2
End Get
Friend Set
m_Product2 = Value
End Set
End Property
Private m_Product2 As Integer
End Class
#End Region
End Namespace

At first we are flattening the records by using a Multikey dictionary (Dictionary<string, List<int>>) inside the Unpivot function. That record we are passing into the Pivot function to get the desired output.

Comments or Responses

Login to post response