Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
xyz_1011
Contributor III
Contributor III

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
Contributor III
Contributor III
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;

View solution in original post

xyz_1011
Contributor III
Contributor III
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
Contributor III
Contributor III
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! 🙂