Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

zagzebski
Contributor

Converting data from columns to rows

Looking for the best way to take this sample data:

ClientJan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14Nov-14Dec-14
Abc Co100125150175200225250275300325350375
123 Inc200210220230240250260270280290300310

And through scripting add a month year field and amount field and turn into:

ClientMonthYearAmount
Abc CoJan-14100
Abc CoFeb-14125
Abc CoMar-14150
Abc CoApr-14175
Abc CoMay-14200
Abc CoJun-14225
Abc CoJul-14250
Abc CoAug-14275
Abc CoSep-14300
Abc CoOct-14325
Abc CoNov-14350
Abc CoDec-14375
123 IncJan-14200
123 IncFeb-14210
123 IncMar-14220
123 IncApr-14230
123 IncMay-14240
123 IncJun-14250
123 IncJul-14260
123 IncAug-14270
123 IncSep-14280
123 IncOct-14290
123 IncNov-14300
123 IncDec-14310

Thanks for any assistance

Steve

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Converting data from columns to rows

Tab:

CrossTable  (MonthYear, Amount)

LOAD * INLINE [

Client, Jan-14, Feb-14, Mar-14, Apr-14, May-14, Jun-14, Jul-14, Aug-14, Sep-14, Oct-14, Nov-14, Dec-14

Abc Co, 100, 125, 150, 175, 200, 225, 250, 275, 300, 325, 350, 375

123 Inc, 200, 210, 220, 230, 240, 250, 260, 270, 280,    290, 300, 310

];

from online help

A cross table 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. To transform a cross table into a straight table, use a crosstable prefix. The result is that the column headers (e.g. 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.

The syntax is:

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

where:

attribute field name is the field to contain the attribute values.

data field name is the field to contain the data values.

n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.

Examples:

Crosstable (Month, Sales) Load * from ex1.csv;

Crosstable (Month,Sales,2) Load * from ex2.csv;

Crosstable (A,B) Select * from table3;

3 Replies
MVP
MVP

Re: Converting data from columns to rows

Tab:

CrossTable  (MonthYear, Amount)

LOAD * INLINE [

Client, Jan-14, Feb-14, Mar-14, Apr-14, May-14, Jun-14, Jul-14, Aug-14, Sep-14, Oct-14, Nov-14, Dec-14

Abc Co, 100, 125, 150, 175, 200, 225, 250, 275, 300, 325, 350, 375

123 Inc, 200, 210, 220, 230, 240, 250, 260, 270, 280,    290, 300, 310

];

from online help

A cross table 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. To transform a cross table into a straight table, use a crosstable prefix. The result is that the column headers (e.g. 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.

The syntax is:

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

where:

attribute field name is the field to contain the attribute values.

data field name is the field to contain the data values.

n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.

Examples:

Crosstable (Month, Sales) Load * from ex1.csv;

Crosstable (Month,Sales,2) Load * from ex2.csv;

Crosstable (A,B) Select * from table3;

aveeeeeee7en
Valued Contributor III

Re: Converting data from columns to rows

Hi Zagzebski

Please see the Attached Qvw.

Regards

Aviral

Not applicable

Re: Converting data from columns to rows

use cross table like this

After TAble name and before Load statement

following Statetment this will solve your problem

crosstable('Month Year',ammount)

Community Browser