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: 
johnso2080
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
hic
Former Employee
Former Employee

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
giakoum
Partner - Master II
Partner - Master II

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;

giakoum
Partner - Master II
Partner - Master II

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;

Gysbert_Wassenaar

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
johnso2080
Contributor
Contributor
Author

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;

giakoum
Partner - Master II
Partner - Master II

a sample application would help

hic
Former Employee
Former Employee

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

johnso2080
Contributor
Contributor
Author

Thanks Henric!