Contents:
=20Optionally, these sums can be performed across groups of values from one= column and broken out in columns based on the values in another. In Designer Cloud Educational, a pivot table is = composed of the following basic elements:
Pivot table element | Description |
---|---|
Column labels | List of one or more columns whose values are rep= resented as the columns in the generated pivot table. |
Row labels | List of one or more columns whose values become = the rows in the generated pivot table. |
Values | Also known as facts, these v= alues are one or more aggregation formulas, which are calculated in the fol= lowing manner: "Show me the value of this formula computed by eac= h row value for every value represented in the generated table." |
NOTE: If your aggregation does not include the kind of = transformation listed above, in which the data is pivoted from rows into co= lumns, you can use the Group By transformation and an aggregate function. S= ee Create Aggregations.
Pivot tables are very powerful tools for summarizing and visualizing lar=
ge-scale volumes of data. In Designer Cl=
oud Educational, search for pivot table
in the Sea=
rch panel to create one.
NOTE: A pivot table completely replaces the source tabl= e. Data that is not captured in the pivot definition is lost.
Tip: In your flows, you may find it useful to create yo= ur pivot tables in independent recipes that are chained from your primary r= ecipe.
Example Data=
Pivot tables are perhaps best explained by example. The following table =
snippet captures transactional data from a number of stores for a range of =
products across a set of dates. Transactional values include total sales, q=
uantity, and cost (POS_Sales
, POS_Qty
, and&n=
bsp;POS_Cost
):
Daily | Store_Nbr | POS_Sales | POS_Qty | POS_Cost | PRODUCT_DESC |
---|---|---|---|---|---|
2/8/13 | 1 | 70 | 7 | 4.97 | ACME LAWN GARDEN BAG CLEAR |
2/7/13 | 2 | 10.62 | 9 | 8.37 | ACME COOKIES CHOC CHIP |
2/7/13 | 2 | 0 | 0 | 0 | ACME SANDWICH BAG |
2/7/13 | 2 | 7.08 | 6 | 5.58 | ACME SODAS SALTED |
2/7/13 | 2 | 3.92 | 2 | 2.82 | ACME SCENTED OIL REFILL-CTRY SUN |
2/7/13 | 2 | 13.44 | 7 | 10.36 | ACME LARGE FUDGE GRAHAMS COOKIES |
2/7/13 | 2 | 0 | 0 | 0 | ACME SUGAR ICE WAFERS VANILLA |
2/7/13 | 3 | 3.16 | 2 | 2.86 | ACME ZOO ANIMAL FRUIT SNACKS 6'S |
2/7/13 | 3 | 3.16 | 2 | 2.78 | ACME WAFERS SUGER ICE |
2/7/13 | 3 | 3.16 | 2 | 2.82 | ACME SCENTED OIL REFILL-CTRY SUN |
2/7/13 | 3 | 6.32 | 4 | 5.92 | ACME RICE CRACKERS ONION |
2/2/13 | 9 | 150 | 30 | 16.2 | ACME FROSTED OATMEAL COOKIE SQUA |
2/2/13 | 9 | 3.5 | 2 | 4.86 | ACME FRUIT SNACK CASTLE ADVENTRS |
2/2/13 | 9 | 90 | 9 | 8.37 | ACME COOKIES CHOC CHIP |
2/2/13 | 9 | 30 | 6 | 3.24 | ACME ASSORTED COOKIES DRP |
2/2/13 | 9 | 70 | 7 | 6.51 | ACME KITCHEN BAG |
2/2/13 | 9 | 170 | 17 | 15.81 | ACME SNACK BAGS RESEALABLE |
2/2/13 | 9 | 20 | 4 | 2.16 | ACME CHEDDARY SN CRACKERS/PROCES |
2/2/13 | 9 | 6.5 | 2 | 8.98 | ACME RICE CRACKERS TERIYAKI |
2/2/13 | 9 | 1.5 | 3 | 1.62 | ACME COOKIE MAPLE LEAF CREME |
2/2/13 | 9 | 30 | 6 | 3.24 | ACME RICE CHIPS CHEDDAR |
2/1/13 | 7 | 190 | 38 | 20.52 | ACME FROSTED OATMEAL COOKIE SQUA |
2/1/13 | 7 | 20 | 2 | 1.86 | ACME COOKIES CHOC CHIP |
2/1/13 | 7 | 10 | 1 | 0.82 | ACME DIGESTIVE RICH TEA BISCUITS |
2/1/13 | 7 | 120 | 24 | 12.96 | ACME ASSORTED COOKIES DRP |
2/1/13 | 7 | 120 | 12 | 11.16 | ACME KITCHEN BAG |
2/1/13 | 7 | 90 | 9 | 8.37 | ACME SNACK BAGS RESEALABLE |
2/1/13 | 7 | 10 | 1 | 0.71 | ACME FUDGE MINT COOKIES SQUARES |
2/1/13 | 7 | 9.5 | 19 | 10.26 | ACME CHEDDARY SN CRACKERS/PROCES |
2/1/13 | 7 | 10 | 1 | 0.82 | ACME COOKIES MAPLE CREAM |
2/1/13 | 7 | 40 | 8 | 4.32 | ACME COOKIE MAPLE LEAF CREME |
The Pivot data transformation supports use of any aggregation function. = For more information, see = Aggregate Functions.
From the above, suppose you are interested in the sales from each store = for each product. You can use the following transformation to compute these= aggregated calculations:
Transformation Name | <= code>Pivot table |
---|---|
Parameter: Column labels | Store_Nbr |
Parameter: Row labels | PRODUCT_DESC |
Parameter: Values | SUM(POS_Sales) |
Parameter: Max number of columns to create | 500 |
In the above transformation:
By default, this transformation generates a maximum of 50 new column= s. However, if the column used for your Column labels contains more than 50= values, you may want to raise this value.
NOTE: Avoid creating datasets wider than 2500 columns. = Very wide datasets can cause performance degradation.
Results:
PRODUCT_DESC | sum_POS_Sales_1 | sum_POS_Sales_2 | sum_POS_Sales_3 | sum_POS_Sales_7 | sum_POS_Sales_9 |
---|---|---|---|---|---|
ACME LAWN GARDEN BAG CLEAR | 70 | 0 | 0 | 0 | 0 |
ACME COOKIES CHOC CHIP | 0 | 10.62 | 0 | 20 | 90 |
ACME SANDWICH BAG | 0 | 0 | 0 | 0 | 0 |
ACME SODAS SALTED | 0 | 7.08 | 0 | 0 | 0 |
ACME SCENTED OIL REFILL-CTRY SUN | 0 | 3.92 | 3.16 | 0 | 0 |
ACME LARGE FUDGE GRAHAMS COOKIES | 0 | 13.44 | 0 | 0 | 0 |
ACME SUGAR ICE WAFERS VANILLA | 0 | 0 | 0 | 0 | 0 |
ACME ZOO ANIMAL FRUIT SNACKS 6'S | 0 | 0 | 3.16 | 0 | 0 |
ACME WAFERS SUGER ICE | 0 | 0 | 3.16 | 0 | 0 |
ACME RICE CRACKERS ONION | 0 | 0 | 6.32 | 0 | 0 |
ACME FROSTED OATMEAL COOKIE SQUA | 0 | 0 | 0 | 190 | 150 |
ACME FRUIT SNACK CASTLE ADVENTRS | 0 | 0 | 0 | 0 | 3.5 |
ACME ASSORTED COOKIES DRP | 0 | 0 | 0 | 120 | 30 |
ACME KITCHEN BAG | 0 | 0 | 0 | 120 | 70 |
ACME SNACK BAGS RESEALABLE | 0 | 0 | 0 | 90 | 170 |
ACME CHEDDARY SN CRACKERS/PROCES | 0 | 0 | 0 | 9.5 | 20 |
ACME RICE CRACKERS TERIYAKI | 0 | 0 | 0 | 0 | 6.5 |
ACME COOKIE MAPLE LEAF CREME | 0 | 0 | 0 | 40 | 1.5 |
ACME RICE CHIPS CHEDDAR | 0 | 0 | 0 | 0 | 30 |
ACME DIGESTIVE RICH TEA BISCUITS | 0 | 0 | 0 | 10 | 0 |
ACME FUDGE MINT COOKIES SQUARES | 0 | 0 | 0 | 10 | 0 |
ACME COOKIES MAPLE CREAM | 0 | 0 | 0 | 10 | 0 |
Suppose you are interested in only in the sum of sales for store numbers=
1-3. To capture a more limited dataset, you can use the SUMIF aggregation function:
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=
=20
=20
=20
Transformation Name <=
code>Pivot table Parameter: Row labels PRODUCT_DESC
Parameter: Values SUMIF(POS_Sales, Store_Nbr<4)
=20
Parameter: Max number of columns to create 500
=20
Most aggregation functions have a conditional (*IF
) variant=
.
Multiple Aggregation Levels<=
/h2>
None of the axes of a pivot table is limited to a single dimension. You =
can have multiple Column labels, Row labels, and Values (formulas). In the =
following transformation, aggregations have been further broken out by date=
, and an additional formula (Value) has been added.
NOTE: Adding multiple Column labels and Values can grea=
tly expand the width of the dataset. Generally, adding Row labels does not =
expand the total count of rows.
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=20
=
=20
=20
=20
Transformation Name <=
code>Pivot table Parameter: Column labels Store_Nbr
Parameter: Row labels1 Date
Parameter: Row labels2 PRODUCT_DESC
Parameter: Values1 SUM(POS_Qty)
Parameter: Values2 SUM(POS_Sales)
Parameter: Max number of columns to create 500
=20
Results:
NOTE: Following results table is incomplete. Some colum=
ns have been omitted for space reasons.
Daily
PRODUCT_DESC
sum_POS_Qty_1
sum_POS_Sales_1
sum_POS_Qty_2
sum_POS_Sales_2
sum_POS_Qty_3
sum_POS_Sales_3
2/8/13
ACME LAWN GARDEN BAG CLEAR
7
70
0
0
0
0
2/7/13
ACME COOKIES CHOC CHIP
0
0
9
10.62
0
0
2/7/13
ACME SANDWICH BAG
0
0
0
0
0
0
2/7/13
ACME SODAS SALTED
0
0
6
7.08
0
0
2/7/13
ACME SCENTED OIL REFILL-CTRY SUN
0
0
2
3.92
2
3.16
2/7/13
ACME LARGE FUDGE GRAHAMS COOKIES
0
0
7
13.44
0
0
2/7/13
ACME SUGAR ICE WAFERS VANILLA
0
0
0
0
0
0
2/7/13
ACME ZOO ANIMAL FRUIT SNACKS 6'S
0
0
0
0
2
3.16
2/7/13
ACME WAFERS SUGER ICE
0
0
0
0
2
3.16
2/7/13
ACME RICE CRACKERS ONION
0
0
0
0
4
6.32
2/2/13
ACME FROSTED OATMEAL COOKIE SQUA
0
0
0
0
0
0
2/2/13
ACME FRUIT SNACK CASTLE ADVENTRS
0
0
0
0
0
0
2/2/13
ACME COOKIES CHOC CHIP
0
0
0
0
0
0
2/2/13
ACME ASSORTED COOKIES DRP
0
0
0
0
0
0
2/2/13
ACME KITCHEN BAG
0
0
0
0
0
0
2/2/13
ACME SNACK BAGS RESEALABLE
0
0
0
0
0
0
2/2/13
ACME CHEDDARY SN CRACKERS/PROCES
0
0
0
0
0
0
2/2/13
ACME RICE CRACKERS TERIYAKI
0
0
0
0
0
0
2/2/13
ACME COOKIE MAPLE LEAF CREME
0
0
0
0
0
0
2/2/13
ACME RICE CHIPS CHEDDAR
0
0
0
0
0
0
2/1/13
ACME FROSTED OATMEAL COOKIE SQUA
0
0
0
0
0
0
2/1/13
ACME COOKIES CHOC CHIP
0
0
0
0
0
0
2/1/13
ACME DIGESTIVE RICH TEA BISCUITS
0
0
0
0
0
0
2/1/13
ACME ASSORTED COOKIES DRP
0
0
0
0
0
0
2/1/13
ACME KITCHEN BAG
0
0
0
0
0
0
2/1/13
ACME SNACK BAGS RESEALABLE
0
0
0
0
0
0
2/1/13
ACME FUDGE MINT COOKIES SQUARES
0
0
0
0
0
0
2/1/13
ACME CHEDDARY SN CRACKERS/PROCES
0
0
0
0
0
0
2/1/13
ACME COOKIES MAPLE CREAM
0
0
0
0
0
0
2/1/13
ACME COOKIE MAPLE LEAF CREME
0
0
0
0
0
0
Group By
If you wish to maintain the original dataset values, you can apply an ag=
gregate function within a single column.
=20
------=_Part_370_1073323140.1680347121576--