topic Peek in QlikView

Peek

johnso2080 — Thu, 02 May 2013 11:38:32 GMT

Hello,

Can anyone advise me on what is wrong with this code/

CustOrders:

NoConcatenate

LOAD DISTINCT [Customer ID],[SalesOrderLine Number],[SalesOrderLine Date]

RESIDENT SalesOrders

ORDER BY [Customer ID],[SalesOrderLine Date] ASC;

LastOrders:

NoConcatenate

LOAD [SalesOrderLine Number], IF(Peek('Customer ID')=[Customer ID],Peek('SalesOrderLine Date'),NULL()) As LastOrderDate

RESIDENT CustOrders

ORDER BY [Customer ID], [SalesOrderLine Date] ASC;

DROP TABLE CustOrders;

I want to create a table 'Last Orders' that contains, for each 'SalesOrderLine Number', the date of the previous order placed by the customer (or Null if there was no previous order).

This script currently gives me a null value for every 'LastOrderDate'

Many Thanks

Darren

Re: Peek

giakoum — Thu, 02 May 2013 11:43:43 GMT

try using above :

LastOrders:

NoConcatenate

LOAD [SalesOrderLine Number],

if(above([Customer ID])=[Customer ID], above([SalesOrderLine Date], null()) As LastOrderDate

RESIDENT CustOrders

ORDER BY [Customer ID], [SalesOrderLine Date] ASC;

Re: Peek

giakoum — Thu, 02 May 2013 11:45:22 GMT

Sorry not above (works only in charts), you need to use previous:

LastOrders:

NoConcatenate

LOAD [SalesOrderLine Number],

if(previous([Customer ID])=[Customer ID], previous([SalesOrderLine Date], null()) As LastOrderDate

RESIDENT CustOrders

ORDER BY [Customer ID], [SalesOrderLine Date] ASC;

Re: Peek

Gysbert_Wassenaar — Thu, 02 May 2013 11:48:50 GMT

I think you need to use the previous() function here instead of peek().

IF(Previous([Customer ID])=[Customer ID],Previous([SalesOrderLine Date]),NULL()) As LastOrderDate

Re: Peek

johnso2080 — Thu, 02 May 2013 12:03:47 GMT

Thanks for your help,

It's still not working using the 'Previous' function as below... Any other possibilities?

CustOrders:

NoConcatenate

LOAD DISTINCT [Customer ID],[SalesOrderLine Number],[SalesOrderLine Date]

RESIDENT SalesOrders

ORDER BY [Customer ID],[SalesOrderLine Date] ASC;

LastOrders:

NoConcatenate

LOAD [SalesOrderLine Number], IF(Previous('Customer ID')=[Customer ID],Previous('SalesOrderLine Date'),NULL()) As LastOrderDate

RESIDENT CustOrders

ORDER BY [Customer ID], [SalesOrderLine Date] ASC;

DROP TABLE CustOrders;

Re: Peek

giakoum — Thu, 02 May 2013 12:09:17 GMT

a sample application would help

Re: Peek

hic — Thu, 02 May 2013 15:09:05 GMT

I can see several problems here...

If you sort Ascending, you will get the first order, not the last.
Peek does not work since you do not have [Customer ID] in the output. So Gysbert is right: Use Previous() instead.
You cannot use 'Customer ID' inside Previous(). You need to quote it with brackets or double quotes: [Customer ID]
The first record within a Customer ID gets NULL as LastOrderDate. But you want it to be a Date, don't you?

Bottom line, I would use

LOAD

[SalesOrderLine Number],

IF(Previous([Customer ID])=[Customer ID],Peek([LastOrderDate]),[SalesOrderLine Date]) As LastOrderDate

RESIDENT CustOrders

ORDER BY [Customer ID], [SalesOrderLine Date] DESC;

HIC

Re: Peek

johnso2080 — Wed, 08 May 2013 09:23:41 GMT

Thanks Henric!