topic Re: Un-cross table in QlikView

Un-cross table

Tue, 11 Jun 2013 10:31:14 GMT

Hi,

I have a field called 'month' and I would like to covert it into a twelve fields. The process would be exacty the inverse as the Crosstable load does.

Does it exists a command to do this or I have to look for the way ?

By the way , does it exists a funtion to convert '01', '02', '03',... into Jan, Feb, Mar,...?

Thanks

Re: Un-cross table

RedSky001 — Tue, 11 Jun 2013 11:36:26 GMT

If you want to create twelve month fields there is no special function that I'm aware of so you could do the following:

If(Month='Jan',Month) as Jan

If(Month='Feb',Month) as Feb

If(Month='Mar',Month) as Mar

etc..

The funciton you need to covert a number of a month to a literal is below.

=Month(Date#('01','MM'))

Re: Un-cross table

Clever_Anjos — Tue, 11 Jun 2013 12:37:50 GMT

You can do this with a for loop, there´s no pre-built function to do that.

Tmp:

LOAD

MonthName(today() - RecNo()) as Month,

Rand() as value

AutoGenerate 300;

YourData:

LOAD null() as n AutoGenerate 0; // fake table to use "Concatenate" onwards

for i = 1 to 12

Let MonthName = SubField('$(MonthNames)',';',$(i)); // MonthNames is a pre-built variable

Concatenate(YourData)

LOAD

if(SubField(Month,' ',1) = '$(MonthName)',value) as $(MonthName)

resident Tmp;

drop Field n;

Re: Un-cross table

hic — Tue, 11 Jun 2013 16:27:47 GMT

There is a built-in function that does this. Generic Load.

HIC