4 Replies Latest reply: Dec 6, 2012 11:48 AM by Matt Morris RSS

    Using peek to accumulate - but not working...

      I have built a facts table the following way:

       

      Load Facts

       

      leftjoin (Facts)

       

      Load ExtraTable1 //includes the field Amount

       

       

      Load Facts2

       

      leftjoin (Facts2)

       

      Load ExtraTable2 //includes the field Amount

       

       

      Concatenate (Facts)

       

      Load

           *,

           numsum ( Amount, Peek( 'Balance') ) as Balance

       

      Resident Facts2

       

       

      When I look at the resultant table in an App I get the following results:

       

      Row NoAmountBalance
      12068.52068.5
      2-456-456
      3236236
      4452.2452.2
      578977897
      6522522
      7-789-789

       

      So for some reason the script isn't adding together Amount with the previous Balance value.  Can anyone help?

       

      Thanks

        • Re: Using peek to accumulate - but not working...
          Gysbert Wassenaar

          It works here. See attached example. The only thing I can think of is that there's a typo in the peek function so the name isn't exactly the same as the fieldname at then end of the line. Like numsum ( Amount, Peek( 'Balence') ) as Balance or numsum ( Amount, Peek( 'balance') ) as Balance

            • Re: Using peek to accumulate - but not working...

              Thanks for the response.  I can't check your code as I'm having issues with our Server licence.  But one thing's for sure there is no typo in my script.  My thoughts are the issue is around the way I have constructed the Facts table; would using the Peek function in a resident table be a problem?

                • Re: Using peek to accumulate - but not working...
                  Gysbert Wassenaar

                  A resident table will work fine with peek (see code below). I still think the peek function isn't using the right field name. QV is case sensitive so balance is not the same as Balance.

                   

                  Data:

                  LOAD * INLINE [

                      Row No    Amount

                      1    2068.5

                      2    -456

                      3    236

                      4    452.2

                      5    7897

                      6    522

                      7    -789

                  ](delimiter is '\t');

                   

                  Result:

                  load *, numsum(Amount, peek('Balance')) as Balance

                  Resident Data;

                   

                  drop table Data;

                   

                  The above gives the same result as this:

                   

                  Data:

                  load *, numsum(Amount,peek('Balance')) as Balance;

                  LOAD * INLINE [

                      Row No    Amount

                      1    2068.5

                      2    -456

                      3    236

                      4    452.2

                      5    7897

                      6    522

                      7    -789

                  ](delimiter is '\t');