Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Multiple ID Column to one ID Row

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

IDQ1
1Red
1Green
2Blue
3Blue
3Green
4Green
4Red
5Red
5Green
5Blue

Table nr2

IDQ1.1Q1.2Q1.3
1RedGreen
2Blue

3BlueGreen
4GreenRed
5RedGreenBlue

Thank you in advance for the help.

Kind regards,

Nuno

Tags (1)
1 Solution

Accepted Solutions

Re: Multiple ID Column to one ID Row

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);

next

drop table Input;


talk is cheap, supply exceeds demand
3 Replies

Re: Multiple ID Column to one ID Row

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);

next

drop table Input;


talk is cheap, supply exceeds demand
Not applicable

Re: Multiple ID Column to one ID Row

Hey Nuno,

have you try generic loads?

nr2:

GENERIC LOAD ID,'Q1.'&ID ,Q1

RESIDENT nr1;

let me know if works..

cheers.

Not applicable

Re: Multiple ID Column to one ID Row

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.

Community Browser