Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everybody,
I am using Qlik Sense Cloud to analyze data that is arranged in a single row & multiple column format. I want to re-arrange it into a single column multiple rows structure. I have used the "Unpivot" function to pivot the data but I am running into a further complication when there are multiple parameters under evaluation in the same sheet.
Can this rearrangement be done using Qlik Sense? If yes, please do let me know how it can be done. The below tables will give you an idea of the initial data format and how I want it:
Initial Data:
Sl.No. | Issue Under Evaluation | Evaluation Criteria 1 | Evaluation Criteria 2 | Evaluation Criteria 3 | Issue Under Evaluation | Evaluation Criteria 1 | Evaluation Criteria 2 | Evaluation Criteria 3 |
1 | 18 | 2 | 1 | 2 | ||||
2 | 18 | 1 | 2 | 19 | 2 | |||
3 | 18 | 1 | 2 | 2 | 2 |
Desired Data Structure:
Sl.No. | Issue Under Evaluation | Evaluation Criteria | Rating |
1 | 18 | Evaluation Criteria 1 | |
1 | 18 | Evaluation Criteria 2 | 2 |
1 | 18 | Evaluation Criteria 3 | |
1 | 1 | Evaluation Criteria 1 | |
1 | 1 | Evaluation Criteria 2 | 2 |
1 | 1 | Evaluation Criteria 3 | |
2 | 18 | Evaluation Criteria 1 | 1 |
2 | 18 | Evaluation Criteria 2 | 2 |
2 | 18 | Evaluation Criteria 3 | |
2 | 19 | Evaluation Criteria 1 | |
2 | 19 | Evaluation Criteria 2 | 2 |
2 | 19 | Evaluation Criteria 3 | |
3 | 18 | Evaluation Criteria 1 | 1 |
3 | 18 | Evaluation Criteria 2 | 2 |
3 | 18 | Evaluation Criteria 3 | |
3 | 2 | Evaluation Criteria 1 | |
3 | 2 | Evaluation Criteria 2 | 2 |
3 | 2 | Evaluation Criteria 3 |
Thank you in advance for your help
Hello,
I think this can be achieved by using a crosstable:
CrossTable([Evaluation criteria], Rating, 2)
load * inline
[
Sl.No., Issue Under Evaluation, Evaluation Criteria 1, Evaluation Criteria 2, Evaluation Criteria 3 ,Issue Under Evaluation, Evaluation Criteria 1, Evaluation Criteria 2 ,Evaluation Criteria 3,
1, 18, , 2, 1, 2
2, 18, 1, 2, 19, 2
3, 18, 1, 2, 2, 2
];
In this case I loaded your initial data in an inline table, but the crosstable prefix causes the resulting table to be the desired output.
Greetings,
Tim
Hello,
I think this can be achieved by using a crosstable:
CrossTable([Evaluation criteria], Rating, 2)
load * inline
[
Sl.No., Issue Under Evaluation, Evaluation Criteria 1, Evaluation Criteria 2, Evaluation Criteria 3 ,Issue Under Evaluation, Evaluation Criteria 1, Evaluation Criteria 2 ,Evaluation Criteria 3,
1, 18, , 2, 1, 2
2, 18, 1, 2, 19, 2
3, 18, 1, 2, 2, 2
];
In this case I loaded your initial data in an inline table, but the crosstable prefix causes the resulting table to be the desired output.
Greetings,
Tim
Thank you so much Tim