3 Replies Latest reply: Jun 21, 2014 4:54 AM by Satyadev Jaiswal RSS

    Pivot Table Question

      Hi All,

       

      I have a question regarding using pivot table. I have a table here:

       

       

      MonthJan 2014(A)Feb 2014(B)Mar 2014(C)Mar 2014(D)Apr 2014(E)May 2014(F)Jun 2014(G)
      DescLetterABCDEFG
      Valid 100150200250300400500

       

      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.

        • Re: Pivot Table Question
          Satyadev Jaiswal

          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.

            • Re: Pivot Table Question

              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.

                • Re: Pivot Table Question
                  Satyadev Jaiswal

                  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