Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Peek

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

1 Solution

Accepted Solutions

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

View solution in original post

7 Replies
Partner
Partner

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;

Partner
Partner

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;

MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Contributor
Contributor

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;

Partner
Partner

a sample application would help

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

View solution in original post

Contributor
Contributor

Thanks Henric!