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

D toc

Excerpt

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
rtrue
, a pivot table is composed of the following basic elements:

...

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_SalesPOS_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).

...

DailyStore_NbrPOS_SalesPOS_QtyPOS_CostPRODUCT_DESCcolumn_1column_2column_3column_9column_7
2/8/1317074.97ACME LAWN GARDEN BAG CLEARYes    
2/7/13210.6298.37ACME COOKIES CHOC CHIP Yes   
2/7/132000ACME SANDWICH BAG Yes   
2/7/1327.0865.58ACME SODAS SALTED Yes   
2/7/1323.9222.82ACME SCENTED OIL REFILL-CTRY SUN Yes   
2/7/13213.44710.36ACME LARGE FUDGE GRAHAMS COOKIES Yes   
2/7/132000ACME SUGAR ICE WAFERS VANILLA Yes   
2/7/1333.1622.86ACME ZOO ANIMAL FRUIT SNACKS 6'S  Yes  
2/7/1333.1622.78ACME WAFERS SUGER ICE  Yes  
2/7/1333.1622.82ACME SCENTED OIL REFILL-CTRY SUN  Yes  
2/7/1336.3245.92ACME RICE CRACKERS ONION  Yes  
2/2/1391503016.2ACME FROSTED OATMEAL COOKIE SQUA   Yes 
2/2/1393.524.86ACME FRUIT SNACK CASTLE ADVENTRS   Yes 
2/2/1399098.37ACME COOKIES CHOC CHIP   Yes 
2/2/1393063.24ACME ASSORTED COOKIES DRP   Yes 
2/2/1397076.51ACME KITCHEN BAG   Yes 
2/2/1391701715.81ACME SNACK BAGS RESEALABLE   Yes 
2/2/1392042.16ACME CHEDDARY SN CRACKERS/PROCES   Yes 
2/2/1396.528.98ACME RICE CRACKERS TERIYAKI   Yes 
2/2/1391.531.62ACME COOKIE MAPLE LEAF CREME   Yes 
2/2/1393063.24ACME RICE CHIPS CHEDDAR   Yes 
2/1/1371903820.52ACME FROSTED OATMEAL COOKIE SQUA    Yes
2/1/1372021.86ACME COOKIES CHOC CHIP    Yes
2/1/1371010.82ACME DIGESTIVE RICH TEA BISCUITS    Yes
2/1/1371202412.96ACME ASSORTED COOKIES DRP    Yes
2/1/1371201211.16ACME KITCHEN BAG    Yes
2/1/1379098.37ACME SNACK BAGS RESEALABLE    Yes
2/1/1371010.71ACME FUDGE MINT COOKIES SQUARES    Yes
2/1/1379.51910.26ACME CHEDDARY SN CRACKERS/PROCES    Yes
2/1/1371010.82ACME COOKIES MAPLE CREAM    Yes
2/1/1374084.32ACME COOKIE MAPLE LEAF CREME    Yes

D s also
inCQLtrue
label((label = "structuring_tasks") OR (label = "column_ui") OR (label = "pivot"))