0 Replies Latest reply: Nov 15, 2016 5:33 AM by Roland Walde RSS

    pivot data in load script

    Roland Walde

      Hi,

      I have a table like this:

      RV:

      LOAD * INLINE [

        DIM1, DIM2 , Week, SOH, TD

        A, A, 201645, 10, 5

        A, A, 201646, 5, 1

        A, A, 201647, 4, 3

        A, A, 201648, 2, 3

        B, A, 201645, 20, 5

        B, A, 201646, 15, 1

        B, A, 201647, 14, 3

        B, A, 201648, 12, 3

      ];

       

      What I need is a final table with this structure:

       

        DIM1, DIM2 , Week, SOH, TD, Week1, Week2, Week3, Week4

        A, A, 201645, 10, 5, 5, 4, 1

        A, A, 201646, 5, 1, 4, 1, -2

        A, A, 201647, 4, 3, 1, -2, -2

        A, A, 201648, 2, 3, 1, 1, 1

        B, A, 201645, 20, 5, 15, 14, 11

        B, A, 201646, 15, 1, 14, 11, 8

        B, A, 201647, 14, 3, 11, 8, 8

        B, A, 201648, 12, 3, 9, 9, 9

       

      For the group of dim1 and dim2, look in each week, calculate:

      Week1: sum(SOH) - rangesum(after(sum(TD),1,1))

      Week1: sum(SOH) - rangesum(after(sum(TD),1,2))

      Week1: sum(SOH) - rangesum(after(sum(TD),1,3))

       

      I can do this calculation in a diagram measure formular,

      but that cost too must time.

      I would like to calculate it in the load script.