Manipulating the data into another form in the script editor

[md_anasabbasi]

Hi All,

I have a data in the following format

Name	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

[sunny_talwar · Accepted solution]

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;

[settu_periasamy]

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;

[sunny_talwar]

Sethu‌ FieldValueCount() function would be more efficient then finding max in a resident load.

[settu_periasamy]

stalwar1‌ Agree. It doesn't came in mind when i worked that. 🙂

[Saravanan_Desingh]

Generic LOAD version..

PFA..

[md_anasabbasi]

Hi sunny Can u plz write a code for me with an optimised way.

Thanks

Anas

[md_anasabbasi]

Couldnt find the attachment saran..

Can U help me out plz

Thanks,

Anas

[sunny_talwar]

It is right here: Re: Manipulating the data into another form in the script editor

[md_anasabbasi]

yeah got it... so sorry