topic Re: I need help with Peak in QlikView

I need help with Peak

Wed, 16 Sep 2015 21:25:51 GMT

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;

Re: I need help with Peak

qlikviewwizard — Thu, 17 Sep 2015 01:22:57 GMT

Hi,

Please check this thread.

Peek() or Previous() ?

Re: I need help with Peak

sasiparupudi1 — Thu, 17 Sep 2015 06:19:04 GMT

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;

Re: I need help with Peak

tresB — Thu, 17 Sep 2015 06:28:44 GMT

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

Re: I need help with Peak

Thu, 17 Sep 2015 15:46:58 GMT

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?

Re: I need help with Peak

sasiparupudi1 — Fri, 18 Sep 2015 08:43:33 GMT

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

Re: I need help with Peak

Fri, 18 Sep 2015 15:44:16 GMT

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

Re: I need help with Peak

Fri, 18 Sep 2015 15:45:04 GMT

Thank you for your help