topic Search next Customer Order Date in App Development

Search next Customer Order Date

Boseca — Tue, 25 Feb 2020 17:46:29 GMT

Hello Everybody.

I need to search the next customer order date and put it in the Order Table and I cannot found an efficient way to do in the Load Script.

Its necessary do this in the load editor because from this table I need to do other actions.

The order table is:

CUSTOMER ID	ORDER ID	ORDER DATE
A	1	01/01/2020
B	2	01/01/2020
A	3	03/01/2020

The Goal is

CUSTOMER ID	ORDER ID	ORDER DATE	NEXT ORDER DATE
A	1	01/01/2020	03/01/2020
B	2	01/01/2020	na
A	3	03/01/2020	na

Any idea?

Thank you very much.

Re: Search next Customer Order Date

mikaelsc — Tue, 25 Feb 2020 18:09:20 GMT

find some inspiration here:

https://community.qlik.com/t5/New-to-QlikView/autonumber-for-client-id-x-order-id/m-p/1678259#M386765

(sort your table by customer and by date descending; then something like if previous customer<>customer,'na',previous(order date)) as next order date

Re: Search next Customer Order Date

Boseca — Wed, 26 Feb 2020 16:35:14 GMT

You are a master!

If anyone need its here find the script used (the group by is because the sales contain the Items inside):

LEFT JOIN (SALES)
LOAD ORDER_ID
,if(Previous(CUSTOMER_ID)<>CUSTOMER_ID,date#('31/12/2999'),Previous(ORDER_DATE )) as NEXT_ORDER_DATE ;
LOAD ORDER_ID, FirstValue(CUSTOMER_ID) AS CUSTOMER_ID, FirstValue(ORDER_DATE ) AS ORDER_DATE
Resident SALES
Group By ORDER_ID
ORDER BY CUSTOMER_ID DESC, ORDER_DATE DESC;