Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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;
Try with single quotes around field name like: peek('S#STRK', -1)
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?
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
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
Thank you for your help