| D toc |
|---|
| Excerpt |
|---|
A pivot table summaries data that is sourced from another table. Using pivot tables, you can calculate aggregating functions, such as sums, maximums, and averages for one or more columns of data. |
Optionally, these sums can be performed across groups of values from one column and broken out in columns based on the values in another. In
| D s product | ||
|---|---|---|
|
...
| Info |
|---|
NOTE: If your aggregation does not include the kind of transformation listed above, in which the data is pivoted from rows into columns, you can use the Group By transformation and an aggregate function. See Create Aggregations. |
...
| Info |
|---|
NOTE: A pivot table completely replaces the source table. Data that is not captured in the pivot definition is lost. |
| Tip |
|---|
Tip: In your flows, you may find it useful to create your pivot tables in independent recipes that are chained from your primary recipe. |
...
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, quantity, and cost (POS_Sales, POS_Qty, and POS_Cost):
...
Most aggregation functions have a conditional (*IF) variant. See Aggregate Functions.
Multiple Aggregation Levels
...
If you wish to maintain the original dataset values, you can perform aggregation calculations apply an aggregate function within a single column. For more information, see Create Aggregations.
Values to Columns
Similar to pivot, the Convert values to columns transformation converts individual values within a column to independent columns in the dataset. For each row, if the value represented by the column is present in the original data, one value is added (e.g. Yes). If it's missing, another value is inserted (e.g. No).
...
| Daily | Store_Nbr | POS_Sales | POS_Qty | POS_Cost | PRODUCT_DESC | column_1 | column_2 | column_3 | column_9 | column_7 |
|---|---|---|---|---|---|---|---|---|---|---|
| 2/8/13 | 1 | 70 | 7 | 4.97 | ACME LAWN GARDEN BAG CLEAR | Yes | ||||
| 2/7/13 | 2 | 10.62 | 9 | 8.37 | ACME COOKIES CHOC CHIP | Yes | ||||
| 2/7/13 | 2 | 0 | 0 | 0 | ACME SANDWICH BAG | Yes | ||||
| 2/7/13 | 2 | 7.08 | 6 | 5.58 | ACME SODAS SALTED | Yes | ||||
| 2/7/13 | 2 | 3.92 | 2 | 2.82 | ACME SCENTED OIL REFILL-CTRY SUN | Yes | ||||
| 2/7/13 | 2 | 13.44 | 7 | 10.36 | ACME LARGE FUDGE GRAHAMS COOKIES | Yes | ||||
| 2/7/13 | 2 | 0 | 0 | 0 | ACME SUGAR ICE WAFERS VANILLA | Yes | ||||
| 2/7/13 | 3 | 3.16 | 2 | 2.86 | ACME ZOO ANIMAL FRUIT SNACKS 6'S | Yes | ||||
| 2/7/13 | 3 | 3.16 | 2 | 2.78 | ACME WAFERS SUGER ICE | Yes | ||||
| 2/7/13 | 3 | 3.16 | 2 | 2.82 | ACME SCENTED OIL REFILL-CTRY SUN | Yes | ||||
| 2/7/13 | 3 | 6.32 | 4 | 5.92 | ACME RICE CRACKERS ONION | Yes | ||||
| 2/2/13 | 9 | 150 | 30 | 16.2 | ACME FROSTED OATMEAL COOKIE SQUA | Yes | ||||
| 2/2/13 | 9 | 3.5 | 2 | 4.86 | ACME FRUIT SNACK CASTLE ADVENTRS | Yes | ||||
| 2/2/13 | 9 | 90 | 9 | 8.37 | ACME COOKIES CHOC CHIP | Yes | ||||
| 2/2/13 | 9 | 30 | 6 | 3.24 | ACME ASSORTED COOKIES DRP | Yes | ||||
| 2/2/13 | 9 | 70 | 7 | 6.51 | ACME KITCHEN BAG | Yes | ||||
| 2/2/13 | 9 | 170 | 17 | 15.81 | ACME SNACK BAGS RESEALABLE | Yes | ||||
| 2/2/13 | 9 | 20 | 4 | 2.16 | ACME CHEDDARY SN CRACKERS/PROCES | Yes | ||||
| 2/2/13 | 9 | 6.5 | 2 | 8.98 | ACME RICE CRACKERS TERIYAKI | Yes | ||||
| 2/2/13 | 9 | 1.5 | 3 | 1.62 | ACME COOKIE MAPLE LEAF CREME | Yes | ||||
| 2/2/13 | 9 | 30 | 6 | 3.24 | ACME RICE CHIPS CHEDDAR | Yes | ||||
| 2/1/13 | 7 | 190 | 38 | 20.52 | ACME FROSTED OATMEAL COOKIE SQUA | Yes | ||||
| 2/1/13 | 7 | 20 | 2 | 1.86 | ACME COOKIES CHOC CHIP | Yes | ||||
| 2/1/13 | 7 | 10 | 1 | 0.82 | ACME DIGESTIVE RICH TEA BISCUITS | Yes | ||||
| 2/1/13 | 7 | 120 | 24 | 12.96 | ACME ASSORTED COOKIES DRP | Yes | ||||
| 2/1/13 | 7 | 120 | 12 | 11.16 | ACME KITCHEN BAG | Yes | ||||
| 2/1/13 | 7 | 90 | 9 | 8.37 | ACME SNACK BAGS RESEALABLE | Yes | ||||
| 2/1/13 | 7 | 10 | 1 | 0.71 | ACME FUDGE MINT COOKIES SQUARES | Yes | ||||
| 2/1/13 | 7 | 9.5 | 19 | 10.26 | ACME CHEDDARY SN CRACKERS/PROCES | Yes | ||||
| 2/1/13 | 7 | 10 | 1 | 0.82 | ACME COOKIES MAPLE CREAM | Yes | ||||
| 2/1/13 | 7 | 40 | 8 | 4.32 | ACME COOKIE MAPLE LEAF CREME | Yes |
| D s also | ||||
|---|---|---|---|---|
|
