Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a question regarding using pivot table. I have a table here:
Month | Jan 2014(A) | Feb 2014(B) | Mar 2014(C) | Mar 2014(D) | Apr 2014(E) | May 2014(F) | Jun 2014(G) | |
Desc | Letter | A | B | C | D | E | F | G |
Valid | 100 | 150 | 200 | 250 | 300 | 400 | 500 |
As you can see, on the table there are 2 Mar 2014. for C and D. I only want to show the latest one, which is (D).
How to determine latest? It's just base on alphabetical order (A<B<C<D<E..etc)
So my question is, if the system detects a common month between the letters it should only show the latest letter. It should not show the previous.
P.S this is not the actual table, this is just an example so I can get an idea.
Thanks.
Hi,
Something like below,
LastValue:
LOAD
Month&'('&Desc&')' as Month,
Desc,
Valid;
LOAD
SubField(Month, '(',1) as Month,
LastValue(Desc) as Desc,
LastValue(Valid) as Valid
Group By SubField(Month, '(',1);
LOAD * INLINE [
Month, Desc, Valid
Jan 2014(A), A, 100
Feb 2014(B), B, 150
Mar 2014(C), C, 200
Mar 2014(D), D, 250
Apr 2014(E), E, 300
May 2014(F), F, 400
Jun 2014(G), G, 500
];
PS: You may have to use Order By clause in your actual table to align data in order.
Hi thanks for the reply. Please do note that I still need to output letter C. It doesn't mean I have to remove it. It still there if I selected it.
How about creating last value flag by using mapping load. See below,
LastValue:
Mapping
LOAD
Month&'('&Desc&')' as Month,
1 as LastValueFlag;
LOAD
SubField(Month, '(',1) as Month,
LastValue(Desc) as Desc
Group By SubField(Month, '(',1);
LOAD * INLINE [
Month, Desc, Valid
Jan 2014(A), A, 100
Feb 2014(B), B, 150
Mar 2014(C), C, 200
Mar 2014(D), D, 250
Apr 2014(E), E, 300
May 2014(F), F, 400
Jun 2014(G), G, 500
];
FactTable:
LOAD Month,
Desc,
Valid,
ApplyMap('LastValue', Month, 0) as LastValueFlag
INLINE [
Month, Desc, Valid
Jan 2014(A), A, 100
Feb 2014(B), B, 150
Mar 2014(C), C, 200
Mar 2014(D), D, 250
Apr 2014(E), E, 300
May 2014(F), F, 400
Jun 2014(G), G, 500
];
And in the pivot table expression, use below script.
Sum ({$<LastValueFlag={1}>} Valid)
Hope it helps you.
Satya