Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

...

DateStateProdIdSales
3/9/16CABig Trike 9000500
3/9/16NVFast GoKart 5000200
3/9/16CASuperQuick Scooter700
3/9/16CAFast GoKart 5000900
3/9/16NVBig Trike 9000300
3/9/16NVSuperQuick Scooter250
3/10/16NVFast GoKart 500050
3/10/16NVBig Trike 9000400
3/10/16NVSuperQuick Scooter150
3/10/16CABig Trike 9000600
3/10/16CASuperQuick Scooter800
3/10/16CAFast GoKart 50001100

Transform Transformation 1: Sum of sales by State for each Date

Apply this transform transformation in Preview only, just so you can see the results:

d-

...

trans
RawWrangletrue
p03Valuesum(Sales)
Typestep
WrangleTextpivot col:Date value:

...

sum(Sales) group:State
p01NameColumn labels
p01ValueDate
p02NameRow labels
p02ValueState
p03NameValues
SearchTermPivot columns

Cancel the transform transformation update.

Statesum_Sales_03/09/2016sum_Sales_03/10/2016
CA21002500
NV750600


Transform 2:
Sum of Sales by Date for each State

d-

...

trans
RawWrangletrue
p03Valuesum(Sales)
Typestep
WrangleTextpivot col:State value:

...

sum(Sales) group:Date
p01NameColumn labels
p01ValueState
p02NameRow labels
p02ValueDate
p03NameValues
SearchTermPivot columns

Datesum_Sales_CAsum_Sales_NV
03/09/20162100750
03/10/20162500600

Cancel the transform transformation update again.

Transform 3: Sum of Sales by product ID for each State

d-

...

trans
RawWrangletrue
p03Valuesum(Sales)
Typestep
WrangleTextpivot col:State value:

...

sum(Sales) group:ProdId
p01NameColumn labels
p01ValueState
p02NameRow labels
p02ValueProdId
p03NameValues
SearchTermPivot columns

ProdIdsum_Sales_CAsum_Sales_NV
Big Trike 90001100700
Fast GoKart 50002000500
SuperQuick Scooter1500300

Example - Multi-column Pivot

The pivot transform The Pivot Columns transformation supports the ability to use the values in multiple columns to specify the columns that are generated. When two or more columns are used in the pivot, columns containing values of all possible combinations from the source columns are generated. 

...

BrandProductMonthSales
AAATowelsJanuary95
AAANapkinsJanuary113
BTowelsJanuary99
BTissuesJanuary88
AAATowelsFebruary108
AAANapkinsFebruary91
BTowelsFebruary85
BTissuesFebruary105
AAATowelsMarch81
AAANapkinsMarch92
BTowelsMarch112
BTissuesMarch104

TransformTransformation:

If you wanted to create summary sales information for each product by month, you might choose to create a pivot on the Product column. However, if you look at the column values, you might notice that the Paper towels product is available for both the AAA and B brands. In this case, you must perform a multi-column pivot on these two columns, like the following:

d-

...

trans
RawWrangletrue
p03Valueaverage(Sales)
Typestep
WrangleTextpivot col:Brand,Product value:

...

average(Sales) group:Month
p01NameColumn labels
p01ValueBrand,Product
p02NameRow labels
p02ValueMonth
p03NameValues
SearchTermPivot columns

Results:

Monthavg_Sales_B_Towelsavg_Sales_B_Tissuesavg_Sales_AAA_Napkinsavg_Sales_AAA_Towels
January998811395
February8510591108
March1121049281

...

StudentIdTestIdScore
s001t00198
s001t00298
s001t00387
s002t00192
s002t00296
s002t00379
s003t00199
s003t00276
s003t00394
s004t00193
s004t00299
s004t00380
s005t00179
s005t00280
s005t00384
s006t00193
s006t00274
s006t00389
s007t00186
s007t00281
s007t00397
s008t00182
s008t00273
s008t00379
s009t00196
s009t00297
s009t00379
s010t00180
s010t00279
s010t00375

TransformTransformation:

For each student, you're interested in a student's average score and maximum score. You create the following pivot transform, which groups computations of average score and maximum score columns by studentId value.

d-

...

trans
RawWrangletrue
p03Value50
Typestep
WrangleTextpivot group: StudentId value: average(Score),max(Score) limit: 50
p01NameRow labels
p01ValueStudentId
p02NameValues
p02Valueaverage(Score),max(Score)
p03NameMax number of columns to create
SearchTermPivot columns

For the generated average column, you want results rounded to two decimal places:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextset col: average_Score value: round(average_Score, 2)
p01NameColumns
p01Valueaverage_Score
p02NameFormula
p02Valueround(average_Score, 2)
SearchTermEdit column with formula

Results:

StudentIdaverage_Scoremax_Score
s00194.3398
s0028996
s00389.6799
s00490.6799
s0058184
s00685.3393
s0078897
s0087882
s00990.6797
s0107880 

...