topic Re: Pick next date if value is null in QlikView

Pick next date if value is null

malmsteen1977 — Sat, 15 Apr 2017 13:59:51 GMT

Hello,

I have the following situation:

WorkOrder	Pos	Date	FINAL
4711	1	-	23.03.2017
4711	2	23.03.2017	23.03.2017
4711	3	-	25.03.2017
4711	4	25.03.2017	25.03.2017
4712	1	12.02.2017	12.02.2017
4712	2	-	12.02.2017
4712	3	17.02.2017	17.02.2017

The table consists of the fields"WorkOrder", "Pos" and "Date". The desired result is the column "FINAL"

If the date in the column "Date" is null, then I want to set the next available date.

Please notice that I have to calculate this with every WorkOrder.

This is an easy example of the data, the real data model is quite difficult, so I can't do the calculation in the script (e.g. with peek)

So please only suggestions with an expression in a diagram.

So long and many thanks in advance,

Re: Pick next date if value is null

swuehl — Sat, 15 Apr 2017 15:04:56 GMT

You could look into chart inter record functions like Below():

WorkOrder	Pos	Date	Alt(Date, below(Date))

4711	1	-	23.03.2017
4711	2	23.03.2017	23.03.2017
4711	3	-	25.03.2017
4711	4	25.03.2017	25.03.2017
4712	1	12.02.2017	12.02.2017
4712	2	-	17.02.2017
4712	3	17.02.2017	17.02.2017

Re: Pick next date if value is null

swuehl — Sat, 15 Apr 2017 15:12:12 GMT

And if you could have more than one Date value missing in WorkOrder / Pos sequence, but your dates are only ascending, you can also combine Below() with a RangeXXX function:

WorkOrder	Pos	Date	Alt(Date, Date(Rangemin(below(Date,1,NoOfRows()))))	Alt(Date,Below(Date))

4711	1	-	23.03.2017	23.03.2017
4711	2	23.03.2017	23.03.2017	23.03.2017
4711	3	-	25.03.2017	25.03.2017
4711	4	25.03.2017	25.03.2017	25.03.2017
4711	6		31.03.2017
4711	7		31.03.2017	31.03.2017
4711	8	31.03.2017	31.03.2017	31.03.2017
4712	1	12.02.2017	12.02.2017	12.02.2017
4712	2	-	17.02.2017	17.02.2017
4712	3	17.02.2017	17.02.2017	17.02.2017

Note that chart inter record functions consider so called column segments, so dimensions and their sort order are important here.

Re: Pick next date if value is null

malmsteen1977 — Tue, 18 Apr 2017 09:15:33 GMT

Hi Stefan,

many thanks, worked out great. I Wasn't thinking of the "alt"-function.