topic Re: Peek & Order by in QlikView

Peek & Order by

Anonymous — Thu, 18 Jul 2013 19:39:49 GMT

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?

Re: Peek & Order by

luciancotea — Thu, 18 Jul 2013 20:13:16 GMT

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.

Re: Peek & Order by

Anonymous — Thu, 18 Jul 2013 20:27:37 GMT

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?

Re: Peek & Order by

luciancotea — Thu, 18 Jul 2013 20:37:09 GMT

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

ORDER BY is applied first.

Re: Peek & Order by

swuehl — Thu, 18 Jul 2013 21:13:05 GMT

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;

...

Re: Peek & Order by

rwunderlich — Fri, 19 Jul 2013 03:44:05 GMT

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

Re: Peek & Order by

Anonymous — Fri, 26 Jul 2013 19:36:55 GMT

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.

Re: Peek & Order by

Thu, 24 Jul 2014 11:00:31 GMT

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;