Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

malmsteen1977
New Contributor

Pick next date if value is null

Hello,

I have the following situation:

WorkOrderPosDateFINAL
47111-23.03.2017
4711223.03.201723.03.2017
47113-25.03.2017
4711425.03.201725.03.2017
4712112.02.201712.02.2017
47122-12.02.2017
4712317.02.201717.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,

1 Solution

Accepted Solutions
MVP
MVP

Re: Pick next date if value is null

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))
 
47111-23.03.201723.03.2017
4711223.03.201723.03.201723.03.2017
47113-25.03.201725.03.2017
4711425.03.201725.03.201725.03.2017
47116 31.03.2017 
47117 31.03.201731.03.2017
4711831.03.201731.03.201731.03.2017
4712112.02.201712.02.201712.02.2017
47122-17.02.201717.02.2017
4712317.02.201717.02.201717.02.2017

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

3 Replies
MVP
MVP

Re: Pick next date if value is null

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

WorkOrder Pos Date Alt(Date, below(Date))
 
47111-23.03.2017
4711223.03.201723.03.2017
47113-25.03.2017
4711425.03.201725.03.2017
4712112.02.201712.02.2017
47122-17.02.2017
4712317.02.201717.02.2017
MVP
MVP

Re: Pick next date if value is null

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))
 
47111-23.03.201723.03.2017
4711223.03.201723.03.201723.03.2017
47113-25.03.201725.03.2017
4711425.03.201725.03.201725.03.2017
47116 31.03.2017 
47117 31.03.201731.03.2017
4711831.03.201731.03.201731.03.2017
4712112.02.201712.02.201712.02.2017
47122-17.02.201717.02.2017
4712317.02.201717.02.201717.02.2017

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

malmsteen1977
New Contributor

Re: Pick next date if value is null

Hi Stefan,

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

Community Browser