Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I start from a table like this:
load * INLINE [
Type, Pck, Provid
1, pck11, prv111
1, pck11, prv112
1, pck12, prv122
2, pck21, prv211
3, pck31, prv311
3, pck31, prv312
3, pck31, prv313
];
What I want to get it's something like:
Type | Pck | Provid1 | Provid2 | Provid3 |
1 | pck11 | prv111 | prv112 | |
1 | pck12 | prv122 | ||
2 | pck21 | prv211 | ||
3 | pck31 | prv311 | prv312 | prv313 |
Basically is creating a record per each pair Type-Pck and populate the remainig columns with its providers
Bear in mind that the maximun number of providers is not fixed.
Can you guys help me?
Thanks a lot in advance
Try this
Table:
LOAD *,
AutoNumber(RowNo(), Pck) as Number;
LOAD * INLINE [
Type, Pck, Provid
1, pck11, prv111
1, pck11, prv112
1, pck12, prv122
2, pck21, prv211
3, pck31, prv311
3, pck31, prv312
3, pck31, prv313
];
FinalTable:
LOAD DISTINCT Type,
Pck
Resident Table;
FOR i = 1 to FieldValueCount('Number')
LET vField = FieldValue('Number', $(i));
Left Join (FinalTable)
LOAD DISTINCT Type,
Pck,
Provid as [Provid$(vField)]
Resident Table
Where Number = $(vField);
NEXT
DROP Table Table;
Try this
Table:
LOAD *,
AutoNumber(RowNo(), Pck) as Number;
LOAD * INLINE [
Type, Pck, Provid
1, pck11, prv111
1, pck11, prv112
1, pck12, prv122
2, pck21, prv211
3, pck31, prv311
3, pck31, prv312
3, pck31, prv313
];
FinalTable:
LOAD DISTINCT Type,
Pck
Resident Table;
FOR i = 1 to FieldValueCount('Number')
LET vField = FieldValue('Number', $(i));
Left Join (FinalTable)
LOAD DISTINCT Type,
Pck,
Provid as [Provid$(vField)]
Resident Table
Where Number = $(vField);
NEXT
DROP Table Table;
Thanks a lot Sunny! Works perfectly