topic Re: howto: transpose in QlikView

howto: transpose

fmazzarelli — Tue, 02 Jun 2015 19:49:25 GMT

hi,

a file excel is organized

Student Genuary February

Fabio 1100 2000

is it possibile transpose a row as column?

MONTH Student t

Genuary 1100

February 2000

Thanks

Re: howto: transpose

rubenmarin — Wed, 03 Jun 2015 06:49:25 GMT

Hi Fabio, you can use CrossTable(), ie:

Data:

CrossTable(Month, Value)

LOAD Student,

Genuary,

February;

LOAD * Inline [

Student,Genuary,February

Fabio,1100,2000

];

This will create a table like:

Month	Student	Value
February	Fabio	2000
Genuary	Fabio	1100

Re: howto: transpose

pokassov — Wed, 03 Jun 2015 06:55:10 GMT

Hello!

Look for CrossTable

Re: howto: transpose

MarcoWedel — Mon, 29 Jun 2015 23:00:56 GMT

Hi,

you might also load using * to avoid hard coded field names:

Data:

CrossTable(Month, Value)

LOAD * Inline [

Student,Genuary,February

Fabio,1100,2000

];

hope this helps

regards

Marco

Re: howto: transpose

MarcoWedel — Mon, 29 Jun 2015 23:06:32 GMT

to get a "real" Month field (no sorting/calculation issues!), you could extend this solution like:

Data:

CrossTable(MonthTemp, Value)

LOAD * Inline [

Student,Genuary,February

Fabio,1100,2000

];

Left Join

LOAD distinct

MonthTemp,

Month(Date#(MonthTemp,'MMMM')) as Month

Resident Data;

Drop Field MonthTemp;

because CrossTable() always treats column names as text.

To implement this solution you could have to define your LongMonthNames variable accordingly (if not already done):

SET LongMonthNames = 'Genuary,February, ... ';

hope this helps

regards

Marco