Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mhouston00
New Contributor III

Peek & Order by

My data looks like this...

Key
YearMonthIDJob
2012|12|10012012121001Manager
2012|12|10022012121002Developer
2013|01|1001201311001Manager
2013|01|1002201311002Developer
2013|02|1001201321001Sr. Manager
2013|02|1002201321002Developer

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|10012012121001ManagerNULL()
2012|12|10022012121002DeveloperNULL()
2013|01|1001201311001Manager

Manager

2013|01|1002201311002DeveloperDeveloper
2013|02|1001201321001Sr. ManagerManager
2013|02|1002201321002DeveloperDeveloper

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Peek & Order by

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;

...

7 Replies
luciancotea
Valued Contributor

Re: Peek & Order by

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.

mhouston00
New Contributor III

Re: Peek & Order by

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
Valued Contributor

Re: Peek & Order by

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

ORDER BY is applied first.

MVP
MVP

Re: Peek & Order by

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;

...

MVP & Luminary
MVP & Luminary

Re: Peek & Order by

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

Highlighted
mhouston00
New Contributor III

Re: Peek & Order by

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. 

Not applicable

Re: Peek & Order by

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;