topic Re: Finding next populated field in QlikView

Finding next populated field

xyz_1011 — Tue, 15 Jun 2021 13:09:18 GMT

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!

Re: Finding next populated field

sunny_talwar — Tue, 15 Jun 2021 14:08:55 GMT

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

Re: Finding next populated field

xyz_1011 — Tue, 15 Jun 2021 14:15:10 GMT

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.

Re: Finding next populated field

sunny_talwar — Tue, 15 Jun 2021 15:14:05 GMT

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;

Re: Finding next populated field

xyz_1011 — Tue, 15 Jun 2021 15:45:18 GMT

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'

Re: Finding next populated field

sunny_talwar — Tue, 15 Jun 2021 15:53:18 GMT

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

Re: Finding next populated field

xyz_1011 — Wed, 16 Jun 2021 04:07:54 GMT

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