topic Re: Using peek to accumulate - but not working... in QlikView

Using peek to accumulate - but not working...

Wed, 05 Dec 2012 17:58:26 GMT

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

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

Gysbert_Wassenaar — Wed, 05 Dec 2012 18:11:41 GMT

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...

Thu, 06 Dec 2012 14:58:12 GMT

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 — Thu, 06 Dec 2012 15:22:03 GMT

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

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

Thu, 06 Dec 2012 16:48:20 GMT

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!