Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
xyz_1011
Partner - Creator II
Partner - Creator II

Finding next populated field

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:

xyz_1011_0-1623762395309.png


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!

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

6 Replies
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
Partner - Creator II
Partner - Creator II
Author

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

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
Partner - Creator II
Partner - Creator II
Author

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'

xyz_1011_0-1623771899869.png

 

sunny_talwar

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

sunny_talwar_0-1623772365626.png

 

xyz_1011
Partner - Creator II
Partner - Creator II
Author

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! 🙂