Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I can see several problems here...
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
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;
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;
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
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;
a sample application would help
I can see several problems here...
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
Thanks Henric!