Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
hirenp0904
Contributor
Contributor

How to move data in one column to multiple columns

Here is the table that i have

ItemName  Region Classification  Value

Item 1            US    Section          Baby

Item 1            US     Aile               1

Item  1           US     Agegroup        2-4

I would like to change this table at load to below

ItemName     Region     Section     Agile     AgeGroup

Item1               US          Baby          1          2-4

Can you suggest what function will help me perform the above task.

1 Solution

Accepted Solutions
Not applicable

Hi Hiren ,

               Please try the below code .

Exa:

LOAD * Inline

[

ItemName,  Region, Classification,  Value

Item1,            US,    Section,          Baby

Item1 ,           US     ,Aile    ,           1

Item1  ,         US     ,Agegroup,        2-4

];

Columnlist:

LOAD Distinct Classification Resident Exa;

Let F='';

Let j=NoOfRows('Columnlist')-1;

for i=0 to j

Let F= F& chr(39)& Peek('Classification',i,'Columnlist')&chr(39) & if(i<>j,',');

NEXT

Set v_attr_list =  $(F);                //'Channel','ChannelPartner','CiscoVar';

let v_first_attr=Peek('Classification',0,'Columnlist');

for Each attr in $(v_attr_list)

if '$(attr)' = '$(v_first_attr)'  then

NewData:

LOAD ItemName,  Region , Value as '$(attr)'

Resident Exa

Where Classification= '$(attr)';

ELSE

Join(NewData)

LOAD ItemName,  Region , Value as '$(attr)'

Resident Exa

Where Classification= '$(attr)';

ENDIF

NEXT

DROP Table Exa;

DROP Table Columnlist;

EXIT Script

Please let me know for the same

Thanks

View solution in original post

3 Replies
Not applicable

Hi Hiren ,

               Please try the below code .

Exa:

LOAD * Inline

[

ItemName,  Region, Classification,  Value

Item1,            US,    Section,          Baby

Item1 ,           US     ,Aile    ,           1

Item1  ,         US     ,Agegroup,        2-4

];

Columnlist:

LOAD Distinct Classification Resident Exa;

Let F='';

Let j=NoOfRows('Columnlist')-1;

for i=0 to j

Let F= F& chr(39)& Peek('Classification',i,'Columnlist')&chr(39) & if(i<>j,',');

NEXT

Set v_attr_list =  $(F);                //'Channel','ChannelPartner','CiscoVar';

let v_first_attr=Peek('Classification',0,'Columnlist');

for Each attr in $(v_attr_list)

if '$(attr)' = '$(v_first_attr)'  then

NewData:

LOAD ItemName,  Region , Value as '$(attr)'

Resident Exa

Where Classification= '$(attr)';

ELSE

Join(NewData)

LOAD ItemName,  Region , Value as '$(attr)'

Resident Exa

Where Classification= '$(attr)';

ENDIF

NEXT

DROP Table Exa;

DROP Table Columnlist;

EXIT Script

Please let me know for the same

Thanks

hirenp0904
Contributor
Contributor
Author

Thanks Miguel

Not applicable

HI Hiren,

             Is this answer is also provided by  somebody else ? because you posted "Thanks Miguel" .

If this solution is working for you dont forget to mark as Correct Answer .

Thanks

Yusuf