topic Re: Converting data from columns to rows in QlikView

Converting data from columns to rows

zagzebski — Sat, 15 Feb 2014 21:25:13 GMT

Looking for the best way to take this sample data:

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

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

Client	MonthYear	Amount
Abc Co	Jan-14	100
Abc Co	Feb-14	125
Abc Co	Mar-14	150
Abc Co	Apr-14	175
Abc Co	May-14	200
Abc Co	Jun-14	225
Abc Co	Jul-14	250
Abc Co	Aug-14	275
Abc Co	Sep-14	300
Abc Co	Oct-14	325
Abc Co	Nov-14	350
Abc Co	Dec-14	375
123 Inc	Jan-14	200
123 Inc	Feb-14	210
123 Inc	Mar-14	220
123 Inc	Apr-14	230
123 Inc	May-14	240
123 Inc	Jun-14	250
123 Inc	Jul-14	260
123 Inc	Aug-14	270
123 Inc	Sep-14	280
123 Inc	Oct-14	290
123 Inc	Nov-14	300
123 Inc	Dec-14	310

Thanks for any assistance

Steve

Re: Converting data from columns to rows

maxgro — Sat, 15 Feb 2014 21:48:39 GMT

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;

Re: Converting data from columns to rows

aveeeeeee7en — Sat, 15 Feb 2014 21:54:44 GMT

Hi Zagzebski

Please see the Attached Qvw.

Regards

Aviral

Re: Converting data from columns to rows

Sun, 16 Feb 2014 09:37:47 GMT

use cross table like this

After TAble name and before Load statement

following Statetment this will solve your problem

crosstable('Month Year',ammount)