I need help with Peak

[#1]

I have been unable to get peak to work they way I understand that it should.  What I am trying to do is fairly simple.  I am loading a table from a QVD. There will be 3 rows for each unique ID. As the table is loading I need to grab the field(s) value from previous rows and add them together.  I assume either I have something wrong in the syntax or I do not correctly understand how Peak is supposed to work.  It is my understanding as the table loads you can Peak back into previous row by indicating how may rows to look back?

Any help is appreciated.

Rob

Simple version of the code I am using.  As you can see since the field name includes a Hash it is housed inside double quotes.

TrackDB:

LOAD "S#STRK" AS S_Attempts,

     "SID" AS "S_SID#",

      SSHTSQ AS S_Attempt_Sq,

      STCODE AS S_Code,

     "SEVT#" AS "S_EVT#",

     STYEAR AS S_Year, 

If(SSHTSQ = 1, "S#STRK",

    if(SSHTSQ = 2, peek("S#STRK", -1) + "S#STRK",

      if(SSHTSQ = 3, peek("S#STRK", -1) + peek("S#STRK", -2) + "S#STRK",  999 ))) AS S_COUNTER

FROM

(QVD)

WHERE STCODE = 'A' AND STYEAR = 2014

order by STCODE, STYEAR, "SEVT#", "SID, SSHTSQ;

[qlikviewwizard]

Hi,

Please check this thread.

Peek() or Previous() ?

[sasiparupudi1]

try like this

TrackDB:

LOAD [S#STRK] AS S_Attempts,

     SID AS [S_SID#],

      SSHTSQ AS S_Attempt_Sq,

      STCODE AS S_Code,

     [SEVT#] AS [S_EVT#],

     STYEAR AS S_Year, 

If(SSHTSQ = 1, [S#STRK],

    if(SSHTSQ = 2, peek([S#STRK], -1) + [S#STRK],

      if(SSHTSQ = 3, peek([S#STRK], -1) + peek([S#STRK], -2) + [S#STRK],  999 ))) AS S_COUNTER

FROM

(QVD)

WHERE STCODE = 'A' AND STYEAR = 2014

order by STCODE, STYEAR, [SEVT#], SID, SSHTSQ;

[tresesco]

Try with single quotes around field name like:              peek('S#STRK', -1)

[#5]

Must be something other than Syntax.  I tried your suggestion along  with every other possible syntax I can think of.  Nothing seems to produce results.

Is my thinking correct in that while the table is loading I should be able to use Peek('field_name', -2) to look at the field value from 2 rows above the one just loaded?

[sasiparupudi1]

yeas it should give you the correct filed values..

try putting the output into a table box  for the following script

LOAD [S#STRK] AS S_Attempts,

     SID AS [S_SID#],

      SSHTSQ AS S_Attempt_Sq,

      STCODE AS S_Code,

     [SEVT#] AS [S_EVT#],

     STYEAR AS S_Year, 

peek([S#STRK], -1) as [S#STRK minus 1],

peek([S#STRK], -2) as [S#STRK minus 2]

FROM

(QVD)

WHERE STCODE = 'A' AND STYEAR = 2014

order by STCODE, STYEAR, [SEVT#], SID, SSHTSQ;

to see if you are getting the correct values

[#7 · Accepted solution]

We were on the right track looking at field names.  What it ended up being is - I needed to use the re-assigned name in the peek statement.  i.e - Peek('S_Attempts', -1)  rather than Peek([S#STRK], -1)

The correct code was

TrackDB:

LOAD "S#STRK" AS S_Attempts,

     "SID" AS "S_SID#",

      SSHTSQ AS S_Attempt_Sq,

      STCODE AS S_Code,

     "SEVT#" AS "S_EVT#",

     STYEAR AS S_Year,

If(SSHTSQ = 1, "S#STRK",

    if(SSHTSQ = 2, peek("S_Attempts", -1) + "S#STRK",

      if(SSHTSQ = 3, peek("S_Attempts", -1) + peek("S_Attempts", -2) + "S#STRK",  999 ))) AS S_COUNTER

FROM

(QVD)

WHERE STCODE = 'A' AND STYEAR = 2014

[#8]

Thank you for your help