Cross Tab
Turn values such as month names into columns so you can compare them side by side. Choose the values that fill each cell and how to combine rows that land in the same cell.
Turn months into columns
Manual Input has four rows with item, month and amount. Cross Tab uses month for the new column names, amount for their values and item to group the rows.
Cross Tab interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| item | Jan | Feb |
|---|---|---|
| Coffee | 30 | |
| Tea | 10 | 20 |
- Change Aggregate to
Count. Each non-NULL amount counts as 1, and Coffee's missing February amount gives 0. - Change Aggregate to
Sum. The original amounts come back because each item and month has just one source row. - Clear Group for
item. The block reports an error because it needs at least one grouping column.
When to use
Use Cross Tab when you want the values in a column, such as month names, as columns you can compare side by side. To go the other way, use Transpose.
Read the output
With the default First aggregation, four input rows become two output rows, one per item. The output has item, Jan and Feb.
Tea has 10 in Jan and 20 in Feb. Coffee has 30 in Jan and NULL in Feb. Rows are sorted by the grouping columns, so Coffee appears first.
month gives the headings and amount gives the values. Neither stays as a separate column.
To turn separate columns into Name and Value rows, use Transpose.
To aggregate rows without creating columns from their values, use Summarize.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
Choose what becomes a column heading, what fills the cells and what identifies each output row. If a chosen key column is missing from the input, the block reports an error. New columns aren't added to a selection you set yourself.
- Name Column
Choose the column whose distinct values become headings. New columns follow the order those values first appear. Missing or blank headings cause an error.
- Value Column
Choose the values that fill the cells. This must differ from Name Column.
- Aggregate
Choose how values sharing a group and heading combine. First and Last keep a source value; Sum and Average combine numeric values; Min and Max choose an extreme. Count counts non-NULL values, and Count distinct counts distinct non-NULL values. Default: First.
- Group
Keep checked the columns that identify each output row. Uncheck a column to combine across it. Name Column and Value Column never group; at least one other column must remain checked.
Cross Tab FAQs
- Is this how I pivot data?
- Yes. This block pivots values into columns inside your flow. To show a pivot table on a dashboard, use the pivot table visual in Vista.
- What if several rows belong in one cell?
- Aggregate decides the result. For two amounts of 10 and 20, First keeps 10, Sum gives 30 and Count gives 2.
- What happens to missing amounts?
- First can keep NULL. Sum and Average ignore NULL values and return NULL when a cell has no other values. Count returns 0 in that case.
- Can I use text values?
- Yes. First and Last can keep text. Sum and Average try to convert text to numbers and report an error if they can't.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.