Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have table like below
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? |
i want to display like below
subject_access_id | sent? | mailed? | status? |
---|---|---|---|
1234 | sent | mailed | true |
1235 | not sent | mailed | false |
1236 | sent |
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 ;
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;
Why don't you use a pivot table to display the output like this? Do you have to change this in the script?
I have to display this records in straight table only. In script or front end both are fine. But i need required output.
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)
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
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.
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
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;
You can do that with Valuelist() in frontend
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.