Peek & Order by

[Anonymous]

My data looks like this...

Key	Year	Month	ID	Job
2012|12|1001	2012	12	1001	Manager
2012|12|1002	2012	12	1002	Developer
2013|01|1001	2013	1	1001	Manager
2013|01|1002	2013	1	1002	Developer
2013|02|1001	2013	2	1001	Sr. Manager
2013|02|1002	2013	2	1002	Developer

I'm trying to use the load script to add a column to this table.  The new column should contain the employee's job from the previous month.  So it should look like this...

Key	Year	Month	ID	Job	Previous Job
2012|12|1001	2012	12	1001	Manager	NULL()
2012|12|1002	2012	12	1002	Developer	NULL()
2013|01|1001	2013	1	1001	Manager	Manager
2013|01|1002	2013	1	1002	Developer	Developer
2013|02|1001	2013	2	1001	Sr. Manager	Manager
2013|02|1002	2013	2	1002	Developer	Developer

I thought that this was exactly what the PEEK() function was for, but I can't get it to work.  Here's what my script looks like...

Temp1:
LOAD
     Key,
     Year,
     Month,
     ID,
     Job
FROM [Excel spreadsheet];
NoConcatenate
Temp2:
LOAD
     Key,
     Year,
     Month,
     ID,
     Job
RESIDENT Temp1
ORDER BY ID, Year, Month;
DROP TABLE Temp1;
NoConcatenate
Final:
LOAD
     Key,
     Year,
     Month,
     ID,
     Job,
     PEEK(Job)  AS Previous_Job
RESIDENT Temp2;
DROP TABLE Temp2;

When I run this and make a Table or List Box that refers to Previous_Job, it shows no values (everything is NULL).  What am I doing wrong?

[luciancotea]

You can do it in one step:

FINAL:
LOAD
     Key,
     Year,
     Month,
     ID,
     Job,
     peek(Job) as PreviousJob
FROM [Excel spreadsheet]
ORDER BY ID, Year, Month;

Peek() and ORDER BY must coexist in the same query.

[Anonymous]

If I use an ORDER BY clause when loading from an Excel Spreadsheet, I get an error that says "Garbage after ".  I have only successfully used ORDER BY when loading from a RESIDENT table.  I've read in the community that this is the only way you can use ORDER BY, although this isn't mentioned in the Qlikview manual.

Also, I thought ORDER BY was applied after all of the commands in the script were executed, so the PEEK() function wouldn't be looking at the right previous record.  Does ORDER BY sort the data before the commands in the load statement are evaluated?

[luciancotea]

My bad, I didn't see xls (copy-paste).

ORDER BY is applied first.

[swuehl · Accepted solution]

I do get values in PreviousJob when using your code (replacing the excel with an inline load), so I can't really reproduce your issue:

Temp1:

LOAD

     Key,

     Year,

     Month,

     ID,

     Job

INLINE [

Key,Year,          Month,          ID,          Job

2012|12|1001,          2012,          12,          1001,          Manager

2012|12|1002,          2012,          12,          1002,          Developer

2013|01|1001,          2013,          1,          1001,          Manager

2013|01|1002,          2013,          1,          1002,          Developer

2013|02|1001,          2013,          2,          1001,          Sr. Manager

2013|02|1002,          2013,          2,          1002,          Developer

];

NoConcatenate

Temp2:

LOAD

     Key,

     Year,

     Month,

     ID,

     Job

RESIDENT Temp1

ORDER BY ID, Year, Month;

DROP TABLE Temp1;

NoConcatenate

Final:

LOAD

     Key,

     Year,

     Month,

     ID,

     Job,

     PEEK(Job)  AS Previous_Job

RESIDENT Temp2;

DROP TABLE Temp2;

I think you can simplify your code to:

Temp1:

LOAD

     Key,

     Year,

     Month,

     ID,

     Job

INLINE [

Key,Year,          Month,          ID,          Job

2012|12|1001,          2012,          12,          1001,          Manager

2012|12|1002,          2012,          12,          1002,          Developer

2013|01|1001,          2013,          1,          1001,          Manager

2013|01|1002,          2013,          1,          1002,          Developer

2013|02|1001,          2013,          2,          1001,          Sr. Manager

2013|02|1002,          2013,          2,          1002,          Developer

];

Final:

LOAD

     Key,

     Year,

     Month,

     ID,

     Job,

     PEEK(Job)  AS Previous_Job

RESIDENT Temp1

ORDER BY ID, Year, Month;

DROP TABLE Temp1;

This will get the previous value of Job into field Previous_Job from your sorted table, so everything seems ok to me,

except that you need to take care of ID changes:

...

Final:

LOAD

     Key,

     Year,

     Month,

     ID,

     Job,

     if(peek(ID)=ID,PEEK(Job)) AS Previous_Job

RESIDENT Temp1

ORDER BY ID, Year, Month;

...

[rwunderlich]

Just to throw another solution into the mix

If your data is already sorted by date -- but necessarily by ID --, you can do this:

Final:

LOAD

          AutoNumber(RecNo(), ID) as Sequence,

          Key, Year,  Month,   ID,  Job

FROM

[http://community.qlik.com/thread/87314?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

LEFT JOIN (Final)

LOAD ID, Sequence+1 as Sequence, Job as PreviousJob

Resident Final

;

DROP FIELD Sequence;

-Rob

http://masterssummit.com

http://robwunderlich.com

[Anonymous]

It turned out that I was referring to my fields by the wrong names.  I had renamed them during load and, since I didn't know that PEEK() acted on the output of the load, I was using the wrong name.  This doesn't show up in my original post because I wanted to simplify the example.  In the end, I sorted first and then used PREVIOUS() and it worked fine. 

[#8]

It seems the Order By statement does not work while loading from an excel file, still there is a workaround.

Try something like this:

Temp:
LOAD A,
B,
C,
D,
E
FROM
ExcelFile.xlsx
(ooxml, no labels, table is Hoja1)
Where A = '1';

FinalTable:
NoConcatenate LOAD
A,
B,
C,
D,
E
Resident Temp
Order by A;    

DROP Table Temp;