Finding next populated field

[xyz_1011]

Hey all,

i am struggling with the following task: In the following example data sets I need to populate the field STATUS (in case its empty), with the (chronologically) next populated value in STATUS for a given %ORDER_ID:

Meaning: For the first two records STATUS would be 'paid' (as per record #3); for record #5 the STATUS would be 'open' (as per record #6) etc

I tried loops, peek(), min() and different joins, but never came up with an accurate result. Any advise, hint and / or help would be much appreciated!

Thanks in advance!

[sunny_talwar]

What would be the STATUS for line 7 (will it be open or paid?) and line 10 (would it be paid or blank)?

[xyz_1011]

Thanks for your feedback @sunny_talwar ! Line 7 would be 'paid' - as per the next following STATUS being 'paid'. Every STATUS of an order's last record stays as is - hence line 10 will be empty.

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    order_id, dt, status
    123, 03/30/2021
    123, 06/30/2021
    123, 09/30/2021, paid
    123, 12/30/2021, paid
    456, 03/30/2021
    456, 06/30/2021, open
    456, 09/30/2021
    456, 12/30/2021, paid
    456, 03/30/2022, paid
    456, 06/30/2022
];

FinalTable:
NoConcatenate
LOAD order_id,
	 dt,
	 If(order_id = Previous(order_id), If(Len(Trim(status)) = 0, Peek('status'), status), status) as status
Resident Table
Order By order_id, dt desc;

DROP Table Table;

[xyz_1011]

Thanks @sunny_talwar  There is still something not proper. Record #5 shows a STATUS of 'paid' although the next record for this order is 'open'

[sunny_talwar]

Something is off in your script... I am seeing this... did you use this? Order By order_id, dt desc;

[xyz_1011]

Hey @sunny_talwar , it didnt work because of my local date & time settings. After formatting the date in dt properly, it seems to be working just fine. Thanks! 🙂