Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
swuehl
MVP
MVP

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;

...

View solution in original post

7 Replies
luciancotea
Specialist
Specialist

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
Not applicable
Author

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
Specialist
Specialist

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

ORDER BY is applied first.

swuehl
MVP
MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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
Author

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;