Cross table

[dinesh842]

Understand Cross table function & how to implement

[Lisa_P]

When you use the crosstable prefix before a load statement it will unpivot the table you are loading.

You must load the column(s) that you want to keep first, then the rest of the columns will be unpivoted, making the column names the field values and the data will be matched. The process will increase the length of the table and decrease the width.

eg

Month | 2018 | 2019 | 2020 | 2021
Jan | 50 | 60 | 70 | 30
Feb | 20 | 25 | 40 | 60
etc

For this data load it would look like this:

Crosstable(Year, Amount)
Load Month, 2018, 2019, 2020, 2021 ...;

This would result in a table

Month | Year | Amount
Jan | 2018 | 50
Feb | 2018 | 20
etc

If you wanted to keep more than one field in the same format, there is an optional 3rd parameter to define how many columns to keep before the twist.