
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
use "where" on pivot table
Hello,
I have a data of payments made by subscribers:
P_SUBSC_NO, INST_# , AMOUNT, ANNOTATION, STATUS
200000000 , 8 , 15 , 122-235 , old
200000000 , 9 , 15 , 122-235 , old
200000000 , 10, 15 , 122-235 , old
200000000 , 11, 15 , 122-235 , old
200000000 , 12, 15 , 122-235 , old
200000000 , 13, 15 , 122-235 , old
200000000 , 14, 15 , 122-235 , current
200000000 , 4 , 69 , 764-183, old
200000000 , 5 , 69 , 764-183, old
200000000 , 6, 69 , 764-183, old
200000000 , 7, 69 , 764-183, old
200000000 , 8, 69 , 764-183, old
200000000 , 9, 69 , 764-183, pending
I want to show on the pivot table the most recent status (the one where the inst_# is the highest)
So, basically it should show on 122-235 the status current and on 764-183 pending
P_SUBSC_NO | ANNOTATION | STATUS | INST_# | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
200000000 | 122-235 | // Error in calculated dimension | 15 | 15 | 15 | 15 | 15 | 15 | 15 | |||||
764-183 | // Error in calculated dimension | 69 | 69 | 69 | 69 | 69 | 69 | 69 |
Thank you
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have done it in script.
Test:
LOAD * INLINE [
P_SUBSC_NO,INST_#,AMOUNT,ANNOTATION,STATUS
200000000 ,8 ,15 ,122-235,old
200000000 ,9 ,15 ,122-235,old
200000000 ,10,15 ,122-235,old
200000000 ,11,15 ,122-235,old
200000000 ,12,15 ,122-235,old
200000000 ,13,15 ,122-235,old
200000000 ,14,15 ,122-235,current
200000000 ,4,69 ,764-183,old
200000000 ,5,69 ,764-183,old
200000000 ,6,69 ,764-183,old
200000000 ,7,69 ,764-183,old
200000000 ,8,69 ,764-183,old
200000000 ,9,69 ,764-183,pending
];
Temp:
Load ANNOTATION as ta,
max(INST_#) as ti
//STATUS as ts
Resident Test
Group by ANNOTATION;
Left join
Load ANNOTATION as ta,INST_# as ti,STATUS as fin_st
Resident Test;
Left Join(Test)
Load ta as ANNOTATION,
ti as INST#,
fin_st as final_status
Resident Temp;
DROP table Temp;
EXIT SCRIPT;
Then In front end
add pivot with SUBSC_NO,ANNOTATION,final_status,INST_# as dimension
and
sum(AMOUNT) as expression.
Regards,
Prashant
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is the expression / Calculated Dimension you have used in STATUS?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have done it in script.
Test:
LOAD * INLINE [
P_SUBSC_NO,INST_#,AMOUNT,ANNOTATION,STATUS
200000000 ,8 ,15 ,122-235,old
200000000 ,9 ,15 ,122-235,old
200000000 ,10,15 ,122-235,old
200000000 ,11,15 ,122-235,old
200000000 ,12,15 ,122-235,old
200000000 ,13,15 ,122-235,old
200000000 ,14,15 ,122-235,current
200000000 ,4,69 ,764-183,old
200000000 ,5,69 ,764-183,old
200000000 ,6,69 ,764-183,old
200000000 ,7,69 ,764-183,old
200000000 ,8,69 ,764-183,old
200000000 ,9,69 ,764-183,pending
];
Temp:
Load ANNOTATION as ta,
max(INST_#) as ti
//STATUS as ts
Resident Test
Group by ANNOTATION;
Left join
Load ANNOTATION as ta,INST_# as ti,STATUS as fin_st
Resident Test;
Left Join(Test)
Load ta as ANNOTATION,
ti as INST#,
fin_st as final_status
Resident Temp;
DROP table Temp;
EXIT SCRIPT;
Then In front end
add pivot with SUBSC_NO,ANNOTATION,final_status,INST_# as dimension
and
sum(AMOUNT) as expression.
Regards,
Prashant
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try to use Calculated Dimension STATUS
=Aggr(If(INST_#=Max(INST_#),STATUS),STATUS)
and Check 'Suppress when value is Null'
Regards

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It returns blank results, not an error though

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Anotonia solution is working well.
You have to use given expression as Calculated Dimension not in Expression.
