Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

md_anasabbasi
New Contributor

Manipulating the data into another form in the script editor

Hi All,

I have a data in the following format

Name
Product

A

P1
AP2
AP3
BP4
BP5
CP6
CP7
CP8
DP9

AND AFTER MANIPULATION IT SHOULD COME IN THIS FORMAT..

NAMEPRODUCT1PRODUCT2PRODUCT3
AP1P2P3
BP4P5-
C

P6

P7P8
DP9--

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

1 Solution

Accepted Solutions
MVP
MVP

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

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;


Capture.PNG

9 Replies
MVP
MVP

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

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;


Capture.PNG

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

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;

MVP
MVP

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

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

stalwar1‌ Agree. It doesn't came in mind when i worked that. :-)

saran7de
Contributor III

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

Generic LOAD version..

PFA..

md_anasabbasi
New Contributor

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

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

Thanks

Anas

md_anasabbasi
New Contributor

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

Couldnt find the attachment saran..

Can U help me out plz

Thanks,

Anas

MVP
MVP

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

md_anasabbasi
New Contributor

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

yeah got it... so sorry

Community Browser