Pivot table - adding new calculated row

[abhi1490]

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

[vchuprina · Accepted solution]

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