Pivot Table Question

[#1]

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.

[Anonymous]

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.

[#3]

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.

[Anonymous]

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