Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

convert rows to columns in qlik

Hi,

i have table like below

subject_access_idinteractivity_type_idvalueinteractivity_type
12341sentsent?
12342mailedmailed?
12343truestatus?
12351not sentsent?
12352mailedmailed?
12353falsestatus?
12361sentsent?

i want to display like below

subject_access_idsent?mailed?status?
1234sentmailedtrue
1235not sentmailedfalse
1236sent

how could i do this?

load subject_access_id, interactivity_type_id ,value, interactivity_type;

SQL SELECT `subject_access_id`,

    `interactivity_type_id`,

    value,

interactivity_type

FROM INTERACTIVITYDATA ;

1 Solution

Accepted Solutions
sunny_talwar

Here is one option

Table:

LOAD * INLINE [

    subject_access_id, interactivity_type_id, value, interactivity_type

    1234, 1, sent, sent?

    1234, 2, mailed, mailed?

    1234, 3, true, status?

    1235, 1, not sent, sent?

    1235, 2, mailed, mailed?

    1235, 3, false, status?

    1236, 1, sent, sent?

];


FinalTable:

LOAD DISTINCT subject_access_id

Resident Table;



FOR i = 1 to FieldValueCount('interactivity_type_id')


LET vField = FieldValue('interactivity_type', $(i));

Left Join (FinalTable)

LOAD DISTINCT subject_access_id,

value as [$(vField)]

Resident Table

Where interactivity_type = '$(vField)';


NEXT


DROP Table Table;

View solution in original post

13 Replies
sunny_talwar

Why don't you use a pivot table to display the output like this? Do you have to change this in the script?

berryandcherry6
Creator II
Creator II
Author

I have to display this records in straight table only. In script or front end both are fine. But i need required output.

OmarBenSalem

As suggested by Sunny, u can simply use a pivot table to display ur data as u want,; but if it's mandatory to transform ur qlik table as follow; please refer to this thread: (reverse crosstable)

https://community.qlik.com/message/78337?&_ga=2.74008722.1029974864.1520419437-968065224.1508498928#...

juraj_misina
Luminary Alumni
Luminary Alumni

I agree with Sunny, you can do this by displaying it in pivot table for most scenarios. However, for some scenarios you need to use The Generic Load

Hope this helps.

Juraj

berryandcherry6
Creator II
Creator II
Author

Hi,

i checked  Generic Load, but after puting generic before load scritp

Generic load subject_access_id, interactivity_type_id ,value, interactivity_type;

SQL SELECT `subject_access_id`,

    `interactivity_type_id`,

    value,

interactivity_type

FROM INTERACTIVITYDATA ;

i am not able to get any field names present in INTERACTIVITYDATA .


should i use for loop logic also? but it mentions its not a good practice to do that.

berryandcherry6
Creator II
Creator II
Author

hi

i looked in to this link it says we can do this by using subfields

Convert Rows to Columns in Load Script

but i am not able to use in my example.

Could anyone explain me that i can use or not ?

Regards,

Supriya

sunny_talwar

Here is one option

Table:

LOAD * INLINE [

    subject_access_id, interactivity_type_id, value, interactivity_type

    1234, 1, sent, sent?

    1234, 2, mailed, mailed?

    1234, 3, true, status?

    1235, 1, not sent, sent?

    1235, 2, mailed, mailed?

    1235, 3, false, status?

    1236, 1, sent, sent?

];


FinalTable:

LOAD DISTINCT subject_access_id

Resident Table;



FOR i = 1 to FieldValueCount('interactivity_type_id')


LET vField = FieldValue('interactivity_type', $(i));

Left Join (FinalTable)

LOAD DISTINCT subject_access_id,

value as [$(vField)]

Resident Table

Where interactivity_type = '$(vField)';


NEXT


DROP Table Table;

zebhashmi
Specialist
Specialist

You can do that with Valuelist() in frontend

juraj_misina
Luminary Alumni
Luminary Alumni

I've used the loop logic to merge tables from generic load into one several times without any issues. Give it a try, see what happens.