Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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").