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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
malmsteen1977
Partner - Contributor
Partner - 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
swuehl
MVP
MVP

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.

View solution in original post

3 Replies
swuehl
MVP
MVP

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
swuehl
MVP
MVP

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
Partner - Contributor
Partner - Contributor
Author

Hi Stefan,

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