Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Search next Customer Order Date

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 IDORDER IDORDER DATE
A101/01/2020
B201/01/2020
A303/01/2020

 

The Goal is

CUSTOMER IDORDER IDORDER DATENEXT ORDER DATE
A101/01/202003/01/2020
B201/01/2020na
A303/01/2020na

 

Any idea?

Thank you very much.

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Search next Customer Order Date

find some inspiration here: 

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

 

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

View solution in original post

2 Replies
Highlighted
Creator III
Creator III

Re: Search next Customer Order Date

find some inspiration here: 

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

 

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

View solution in original post

Highlighted
Contributor II
Contributor II

Re: Search next Customer Order Date

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;