Pivot
The Pivot node is used to convert tabular format grid (generally referred to as a table or list) into a cross tabulated grid (often referred to as 'crosstab' or matrix grid). Cross tabulation is used to organize and aggregate raw categorical data into a grid format, which is useful for comparing and analyzing data. If your source data contains tabular data, as is often the case with databases; and you need to convert these to a matrix grid, this is done with the 'Pivot' node.
How to Pivot a Table
Connect the Pivot node to the table node containing the tabular grid. Go to the Properties panel to configure the node:
- Pivot Columns Column: Select the column to be used as the matrix column. This means that the values in this column will become column headings in the matrix.
- Pivot Values Column: Select the required values column (measure) that will represent the values in the matrix.
- Sample fetch Limit: Define the sample size to be used when fetching data from the table. The more rows chosen will result in a more accurate result, but will take longer to run.
- Unlimited (Slow): Select the unlimited checkbox (blue arrow) to fetch all rows of data for the sample. This will take longer to run.
- Auto Detect: Click the auto detect button (green arrow) to automatically detect the correct column names.
- Add Column Name (+ sign): Click the plus sign (yellow arrow) to manually add column names that might not be detected due to a smaller sample being used or to cater for future requirements.
Example
