Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
abhi1490
Contributor II
Contributor II

Pivot table - adding new calculated row

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

abhi1490_0-1651732445790.png

 

This is what i need :

 

abhi1490_1-1651732572993.png

Any suggestion on how this can be achieved? 

Thank you in advance for your help. 

-Abhishek

Labels (1)
1 Solution

Accepted Solutions
vchuprina
Specialist
Specialist

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.

vchuprina_0-1651819930776.png

Measure – SUM({<Type={'Data'}>}Measure). Only data A and B are visible.

vchuprina_1-1651820064457.png

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").

View solution in original post

1 Reply
vchuprina
Specialist
Specialist

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.

vchuprina_0-1651819930776.png

Measure – SUM({<Type={'Data'}>}Measure). Only data A and B are visible.

vchuprina_1-1651820064457.png

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").