Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a data in the following format
| Product | |
---|---|---|
A | P1 | |
A | P2 | |
A | P3 | |
B | P4 | |
B | P5 | |
C | P6 | |
C | P7 | |
C | P8 | |
D | P9 |
AND AFTER MANIPULATION IT SHOULD COME IN THIS FORMAT..
NAME | PRODUCT1 | PRODUCT2 | PRODUCT3 |
---|---|---|---|
A | P1 | P2 | P3 |
B | P4 | P5 | - |
C | P6 | P7 | P8 |
D | P9 | - | - |
I have been trying approaches but couldn't find a solution.
If there are 'n' numbers of product in Name 'A' then number of columns should also be 'n'
No matter how many products are associated with Name 'B'
Thanks & Regards,
Anas
Besides using Generic keyword before the LOAD statement, you can try this also
Table:
LOAD AutoNumber(RowNo(), F1) as Key,
F1 as Name,
Product
FROM
[https://community.qlik.com/thread/217237]
(html, codepage is 1252, embedded labels, table is @2);
FinalTable:
LOAD Distinct Name
Resident Table;
For i = 1 to FieldValueCount('Key')
Join (FinalTable)
LOAD Name,
Product as Product$(i)
Resident Table
Where Key = $(i);
NEXT
DROP Table Table;
Besides using Generic keyword before the LOAD statement, you can try this also
Table:
LOAD AutoNumber(RowNo(), F1) as Key,
F1 as Name,
Product
FROM
[https://community.qlik.com/thread/217237]
(html, codepage is 1252, embedded labels, table is @2);
FinalTable:
LOAD Distinct Name
Resident Table;
For i = 1 to FieldValueCount('Key')
Join (FinalTable)
LOAD Name,
Product as Product$(i)
Resident Table
Where Key = $(i);
NEXT
DROP Table Table;
May be try this..
T1:
LOAD *,AutoNumber(RecNo(),Name) as Key;
LOAD * INLINE [
Name, Product
A, P1
A, P2
A, P3
B, P4
B, P5
C, P6
C, P7
C, P8
D, P9
];
NoConcatenate
Final:
LOAD Distinct Name Resident T1;
MaxKeyfind:
LOAD Max(Key) as MaxKey Resident T1;
Let vMaxKey=Peek('MaxKey');
DROP Table MaxKeyfind;
FOR i = 1 to $(vMaxKey)
Left Join(Final)
LOAD Name,Product as Product$(i) Resident T1 Where Key=$(i);
NEXT i
DROP Table T1;
Sethu FieldValueCount() function would be more efficient then finding max in a resident load.
stalwar1 Agree. It doesn't came in mind when i worked that. 🙂
Generic LOAD version..
PFA..
Hi sunny Can u plz write a code for me with an optimised way.
Thanks
Anas
Couldnt find the attachment saran..
Can U help me out plz
Thanks,
Anas
It is right here: Re: Manipulating the data into another form in the script editor
yeah got it... so sorry