1 Reply Latest reply: Feb 9, 2017 9:49 AM by Sunny Talwar RSS

    Peek function for more than one dimension

    Fredrik Quirin

      Hi,

       

      I am trying to create values for every number in my table using the peek function. It's working with only one dimension but not with two. This is my input:

       

      LOAD * INLINE [

          No, Item, Value

          1, A, 100

          1, B, 50

          4, A, 200

          4, B, 300

          5, A, 99

          5, B, 400  

      ];

       

      and I want the output to look like this:

          

      NoItemAB
      1 10050
      2 10050
      3 10050
      4 200300
      5 99400
      6 99400

       

      I have tried with the following script:

      NumberTable:

      Load RecNo() as No

      AutoGenerate (6);

       

       

      Left join

      LOAD * INLINE [

          No, Item, Value

          1, A, 100

          1, B, 50

          4, A, 200

          4, B, 300

          5, A, 99

          5, B, 400  

      ];

       

      //NewTable:

      NoConcatenate

      Load No,

           if(IsNull(Item),Peek(Item),Item) as Item,

           if(IsNull(Value),Peek(Value),Value) as Value

      Resident NumberTable

      Order by No, Item Asc;

       

      Drop Table NumberTable;

       

      But the peek function is only working for Item B:

          

      NoItemAB
      1 10050
      2 -50
      3 -50
      4 200300
      5 99400
      6 -400
        • Re: Peek function for more than one dimension
          Sunny Talwar

          Try this:

           

          NumberTable:

          Load RecNo() as No

          AutoGenerate (6);

           

          Left Join (NumberTable)

          LOAD * Inline [

          Item

          A

          B

          ];

           

          Left join

          LOAD * INLINE [

              No, Item, Value

              1, A, 100

              1, B, 50

              4, A, 200

              4, B, 300

              5, A, 99

              5, B, 400 

          ];

           

          NewTable:

          NoConcatenate

          Load No,

               Item,

               if(IsNull(Value),Peek(Value),Value) as Value

          Resident NumberTable

          Order by Item, No;

           

          DROP Table NumberTable;