Turn columns into rows

[Anonymous]

Hello,

I have done the below before but I can't remember the right load-script code to do it so I hope someone can help me 🙂

This is an example of a base table I have:

Date	Country	Score A	Score B
01/01/2016	Belgium	10	20
01/01/2016	France	10	20

Would need to be converted to this:

Date	Country	Dimension	Values
01/01/2016	Belgium	Score A	10
01/01/2016	Belgium	Score B	20
01/01/2016	France	Score A	10
01/01/2016	France	Score B	20

If I recall well, I had to set a certain cut-off point in my load script, stating the number of columns to take into account "as-is", after which all trailing columns would be considered new values, instead of columns.

Any clue would be greatly appreciated 🙂

Kind regards,

Christophe

[awhitfield]

Looks like a crosstable:

Crosstable

The crosstable  prefix is used to turn a cross table into a straight table, that is, a wide table with many columns is turned into a tall table, with the column headings being placed into a single attribute column.

Syntax:

crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )  

Arguments:

Argument	Description
attribute field name	The field that contains the attribute values.
data field name	The field that contains the data values.
n	The number of qualifier fields preceding the table to be transformed to generic form. Default is 1.

A crosstable is a common type of table featuring a matrix of values between two or more orthogonal lists of header data, of which one is used as column headers. A typical example could be to have one column per month. The result of the crosstable prefix is that the column headers (for example month names) will be stored in one field, the attribute field, and the column data (month numbers) will be stored in a second field: the data field.

Example:

Crosstable (Month, Sales, 2) LOAD * INLINE[

Person, Location, Jan, Feb, Mar

Bob, London, 100, 200, 300

Kate, New York, 400, 500, 600

];

Result:  

Person,	Location,	Month,	Sales
Bob,	London,	Jan,	100
Bob,	London,	Feb,	200
Bob,	London,	Mar,	300
Kate,	New York,	Jan,	400
Kate,	New York,	Feb,	500
Kate,	New York,	Mar,	600

Crosstable wizard

The crosstable wizard is dialog driven method of creating the crosstable statement. This dialog is opened by clicking the Crosstable  button in the Options page of the File Wizard. The crosstable wizard contains the following options:

Qualifier Fields	The number of qualifier fields that precede the fields to be transformed.
Attribute Field	The name of the new field that will contain all the fields (attribute values) to be transformed.
Data Field	The name of the new field that will contain the data of the attribute values.

[#3]

use Crosstable

Temp:

Load * Inline [

Date, Country, Score A, Score B

01/01/2016, Belgium, 10, 20

01/01/2016, France, 10, 20

];

Final:

CrossTable(Dimension, Values, 2)

Load *

Resident Temp;

Drop table Temp;

[Anonymous]

Thanks guys, this was indeed the syntax I was looking for !!!