Multiple ID Column to one ID Row

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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;

[#3]

Hey Nuno,

have you try generic loads?

nr2:

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

RESIDENT nr1;

let me know if works..

cheers.

[#4]

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.