Show all orders, if the order of the same customer

[steasy]

Hello,

I have the following table in my script (but with more different customers and their latest orders):

Table:

LatestOrder	CustomerID	OrderID	Paid
-	12345	101	-
-	12345	102	X
-	12345	103	X
X	12345	104	-
-	67890	105	-
-	67890	106	X
X	67890	108	-

What I want to see at the surface are all latest orders by customers, IF the order right before is paid:

LatestOrder	CustomerID	OrderID	Paid
-	12345	103	X
X	12345	104	-
-	67890	106	X
X	67890	108	-

I'm quite desperate, because I tried it with the Previous()-function in script and also with the aggr() at the surface table. I'm always missing something to get exactly those two orders.

Also see the orderID for customer 67890. Order 107 got cancelled, that's why it's not shown in my table. That's why I want to check the "Paid" state for the order right before the latest order that didn't get cancelled.

That's the kind of data I have to deal with right now, so maybe you have a good idea. I've spent like 4 hours today just on that formular.

Thanks in advance!

[QFabian · Accepted solution]

Hi @steasy , please try this example, it is not exactly as your expected table, but it is close.

And iit s using previous inter record function

Script :

Aux:

Load * INLINE [

LatestOrder, CustomerID, OrderID, Paid

, 12345 , 101,

, 12345 , 102, X

, 12345 , 103, X

X, 12345 , 104,

, 67890 , 105,

, 67890 , 106, X

X, 67890 , 108,

];

qualify *;

Data:

Load 

* ,

    if((previous(CustomerID) = CustomerID and previous(Paid) = 'X') or 

    (previous(CustomerID) = CustomerID and Paid = 'X')

    , 1, 0)  as Id

Resident Aux;

exit script;

Results

[steasy]

Hi @QFabian, 

that's it, thank you so much!