Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Community,
I need help with this scenario related to Pivot Table. I have been looking around for some hint but no luck.
I have two fields- Field 1 and Field 2. Field 1 contains value A and B. Field 2 contains value P, Q and R. Both the fields have string as data type.
Field1 Field2
A P
B Q
R
This is the current scenario
This is what i need :
Any suggestion on how this can be achieved?
Thank you in advance for your help.
-Abhishek
Hi,
You can calculate ‘A + B’ in your script and concatenate it to the main table.
Also, it makes sense to add a hardcoded field, that will help to show/hide ‘A+B’ if needed.
DataTMP:
LOAD *,
'Data' AS Type; // Hardcoded field. Use it in set analysis
LOAD * Inline[
Field1, Field2, Measure,
A, P, 100
A, Q, 130
A, R, 90
B, P, 60
B, Q, 220
B, R, 190
];
Concatenate(DataTMP)
LOAD
'A + B' AS Field1,
Field2,
SUM(Measure) AS Measure,
'Total' AS Type // Hardcoded field. Use it in set analysis
Resident DataTMP
Group By Field2;
Result:
Measure – Sum(Measure). Everything is visible.
Measure – SUM({<Type={'Data'}>}Measure). Only data A and B are visible.
Regards,
Vitalii
Hi,
You can calculate ‘A + B’ in your script and concatenate it to the main table.
Also, it makes sense to add a hardcoded field, that will help to show/hide ‘A+B’ if needed.
DataTMP:
LOAD *,
'Data' AS Type; // Hardcoded field. Use it in set analysis
LOAD * Inline[
Field1, Field2, Measure,
A, P, 100
A, Q, 130
A, R, 90
B, P, 60
B, Q, 220
B, R, 190
];
Concatenate(DataTMP)
LOAD
'A + B' AS Field1,
Field2,
SUM(Measure) AS Measure,
'Total' AS Type // Hardcoded field. Use it in set analysis
Resident DataTMP
Group By Field2;
Result:
Measure – Sum(Measure). Everything is visible.
Measure – SUM({<Type={'Data'}>}Measure). Only data A and B are visible.
Regards,
Vitalii