Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
johnso2080
New 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

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Peek

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

7 Replies
giakoum
Honored Contributor II

Re: Peek

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
Honored Contributor II

Re: Peek

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

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
New Contributor

Re: Peek

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
Honored Contributor II

Re: Peek

a sample application would help

Employee
Employee

Re: Peek

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
New Contributor

Re: Peek

Thanks Henric!

Community Browser