Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Hey Nuno,
have you try generic loads?
nr2:
GENERIC LOAD ID,'Q1.'&ID ,Q1
RESIDENT nr1;
let me know if works..
cheers.
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.