topic Re: Multiple ID Column to one ID Row in QlikView

Multiple ID Column to one ID Row

Thu, 18 Jul 2013 16:31:02 GMT

Hi guys,

I want to transform table nr1 into table nr2 throught scripting.

This example is with 3 columns but they should be as many as the maximum cases for one ID.

Table nr1

ID	Q1
1	Red
1	Green
2	Blue
3	Blue
3	Green
4	Green
4	Red
5	Red
5	Green
5	Blue

Table nr2

ID	Q1.1	Q1.2	Q1.3
1	Red	Green
2	Blue
3	Blue	Green
4	Green	Red
5	Red	Green	Blue

Thank you in advance for the help.

Kind regards,

Nuno

Re: Multiple ID Column to one ID Row

Gysbert_Wassenaar — Thu, 18 Jul 2013 16:53:01 GMT

It's a bad idea because it messes up your data model. You should keep the data as it is and use a pivot table chart object to show it in the format that table nr2 has.

But, if you insist....

Input:

load ID, Q1,if(previous(ID)=ID,peek('Ctr')+1,1) as Ctr INLINE [

ID, Q1

1, Red

1, Green

2, Blue

3, Blue

3, Green

4, Green

4, Red

5, Red

5, Green

5, Blue

];

Result:

load 1 as ID AutoGenerate 1;

for i = 1 to FieldValueCount('Ctr')

join (Result)

load ID, Q1 as 'Q1.$(i)'

Resident Input

where Ctr = $(i);

drop table Input;

Re: Multiple ID Column to one ID Row

Thu, 18 Jul 2013 20:56:16 GMT

Hey Nuno,

have you try generic loads?

nr2:
GENERIC LOAD ID,'Q1.'&ID ,Q1
RESIDENT nr1;

let me know if works..

cheers.

Re: Multiple ID Column to one ID Row

Sun, 21 Jul 2013 11:45:38 GMT

Thanks a lot.

You are right, but this is not for the data model. I just need to to automate some outputs for third party aplications.