Using peek to accumulate - but not working...

[#1]

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 No	Amount	Balance
1	2068.5	2068.5
2	-456	-456
3	236	236
4	452.2	452.2
5	7897	7897
6	522	522
7	-789	-789

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

Thanks

[Gysbert_Wassenaar · Accepted solution]

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

[#3]

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?

[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');

[#5]

Ok with your feedback I decided to go back and look at how I was constructing my tables.  Turns out I had it wrong so I went back and adjusted.  My numsum ( Amount, Peek( 'Balance') ) as Balance is now working

Thanks for making me question my script again!