Table of Contents
SHIFT COLUMN UP/DOWN
Category: Transform / Advanced
Description
This action shifts values in the specified column up or down. When a column is shifted down, empty cells are inserted at the top of the column. When shifted up, empty cells are inserted at the bottom.
Use cases
This action can be used in cases when a comparison with a previous/future period is required.
Action settings
| Setting | Description |
|---|---|
| Column | Select the column shift. |
| Shift direction | Select whether the column's rows will be shifted up or down. Options: Up or Down. |
| Shift by N rows* | Select or enter the number of rows to be shifted by. |
| Mode | Select whether the results of this action will overwrite the selected column's content, or if a new column will be created. Options: Create new column (and provide a column name) or Replace column. |
* Setting can be specified using a parameter.
Examples
Example: Calculate the delta with previous year. For this, we're appending a new column ("Prev. year sales"), shifted down by 1 row.
Source data: Sales by year.
| Year | Sales |
|---|---|
| 2010 | 10000 |
| 2011 | 12100 |
| 2012 | 9000 |
| 2013 | 11000 |
| 2014 | 8500 |
| 2015 | 11300 |
Action parameters:
Column is "Sales"
Shift "Down"
Shift by 1 row
Mode is "Create new column", named "Prev. year sales"
Result table:
| Year | Sales | Prev. year sales |
|---|---|---|
| 2010 | 10000 | |
| 2011 | 12100 | 10000 |
| 2012 | 9000 | 12100 |
| 2013 | 11000 | 9000 |
| 2014 | 8500 | 11000 |
| 2015 | 11300 | 8500 |
Community examples
- Puzzler: derived column from two columns (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 4)
- Aggregation of consecutive date ranges (Project; Module: Main; Group: Tab 1; Table: Easymorph Patient Example Data.xlsx;
Action position: 3) - Read Data with two line header (Project; Module: Main; Group: Tab 1; Table: Read, Mark groups; Action position: 8)