Forum Discussion
Customer next step
@amitchandak My data
CustomerId DATE START END JOURNEY_STATE
1111 2020-01-02 2020-01-02 22:16:23.000 2020-01-02 22:19:02.000 Arrival
1111 2020-01-02 2020-01-02 22:19:03.000 2020-01-02 22:23:17.000 Enquiry
2222 2020-01-02 2020-01-02 15:48:21.000 2020-01-02 15:49:09.000 Arrival
2222 2020-01-02 2020-01-02 15:49:10.000 2020-01-02 15:52:28.000 Navigation1
I create a new column like :
Is nav = if(ISBLANK( MiNX(FILTER(journey,journey[START]>EARLIER(journey[end]) && journey[CustomerId]=EARLIER(journey[CustomerId]) && journey[JOURNEY_STATE] = "Navigation1"),journey[START])),"No","Yes")
You can modify as per need
File is attached below signature . if you need more help make me @
Appreciate your Kudos.
amitchandak
I think we should not use the journey, [START]>EARLIER(journey[end]) see the above fig.
co column, define the next step of the customer.
In the above data, the customer started his journey at arrival 2:10:07 Pm and next he went to Enquiry 10:55:16 PM in that column co the value should be Enquiry I am not getting which constraint we have to add to get that values.manideep547 , Can you share this data with the expected output? Let me check again
amitchandak
values in co column are like below
1]Enquiry
2]Departure
3]Navigation2
4]Navigation2
5]Navigation2
6]Navigation2
7]Navigation2
8]Navigation2
9]Navigation2
10]Navigation2
11]Navigation2
12]Departure
13]Navigation2
amitchandak Here customer Id is repeating so we can't us MAX &MIN function to get next values in co column
In SQL we can achieve this by using a lag function it gives the next values of the same customer.
If we use MAX then, we have to compare only two rows of the customer.