use "where" on pivot table

[#1]

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

[MK_QSL]

What is the expression / Calculated Dimension you have used in STATUS?

[PrashantSangle · Accepted solution]

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

[antoniotiman]

Try to use Calculated Dimension STATUS

=Aggr(If(INST_#=Max(INST_#),STATUS),STATUS)

and Check 'Suppress when value is Null'

Regards

[#5]

It returns blank results, not an error though

[antoniotiman]

[tyagishaila]

Anotonia solution is working well.

You have to use given expression as Calculated Dimension not in Expression.