topic Re: Manipulating the data into another form in the script editor in Qlik NPrinting

Manipulating the data into another form in the script editor

md_anasabbasi — Tue, 17 May 2016 15:35:41 GMT

Hi All,

I have a data in the following format

Name

Product

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

Re: Manipulating the data into another form in the script editor

sunny_talwar — Tue, 17 May 2016 16:28:36 GMT

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;

Re: Manipulating the data into another form in the script editor

settu_periasamy — Tue, 17 May 2016 16:36:15 GMT

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;

Re: Manipulating the data into another form in the script editor

sunny_talwar — Tue, 17 May 2016 16:37:46 GMT

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

Re: Manipulating the data into another form in the script editor

settu_periasamy — Tue, 17 May 2016 16:44:04 GMT

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

Re: Manipulating the data into another form in the script editor

Saravanan_Desingh — Tue, 17 May 2016 17:09:41 GMT

Generic LOAD version..

PFA..

Re: Manipulating the data into another form in the script editor

md_anasabbasi — Wed, 18 May 2016 12:12:16 GMT

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

Thanks

Anas

Re: Manipulating the data into another form in the script editor

md_anasabbasi — Wed, 18 May 2016 12:13:19 GMT

Couldnt find the attachment saran..

Can U help me out plz

Thanks,

Anas

Re: Manipulating the data into another form in the script editor

sunny_talwar — Wed, 18 May 2016 15:33:14 GMT

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

Re: Manipulating the data into another form in the script editor

md_anasabbasi — Thu, 19 May 2016 09:15:39 GMT

yeah got it... so sorry