Chart table - if current row value is null, use the last non null value

[colinodonnel]

Hello there,

I have some data with missing values.

In the charts, I would like the last value to be used until replaced by a new value, just like the proposedvalue below.

(note that the field proposedvalue  does not exist in the data and has been added to for this example)

This needs to be a chart based formula only please.

Edit - a test desktop app is now attached.

Many thanks,

Colin

Months:
Load
date(Monthdate)as Monthdate,
num(value) as value,
num(proposedvalue) as proposedvalue
;
Load * inline [
Monthdate, value, proposedvalue
01/01/2019, 1,1
01/02/2019,  , 1
01/03/2019,  , 1
01/04/2019,  , 1
01/05/2019, , 1
01/06/2019, 2, 2
01/07/2019,  , 2
01/08/2019,  , 2
01/09/2019, 1, 1
01/10/2019, , 1
01/11/2019, , 1
01/12/2019,  , 1
]
;

[JordyWegman]

Hi Colin,

Try this:

Months:
Load
   date(Monthdate)as Monthdate,
   num(value) as value,
   If(Isnull(value),Num(Peek(proposedvalue)),Num(value)) as proposedvalue
;
Load * inline [
Monthdate, value, proposedvalue
01/01/2019, 1,1
01/02/2019,  , 1
01/03/2019,  , 1
01/04/2019,  , 1
01/05/2019, , 1
01/06/2019, 2, 2
01/07/2019,  , 2
01/08/2019,  , 2
01/09/2019, 1, 1
01/10/2019, , 1
01/11/2019, , 1
01/12/2019,  , 1
]
;

Jordy

Climber

[colinodonnel]

Hi Jordy,

Thanks for the reply, but the solution needs to be done in the Chart only and not via load editor.

Have been trying firstsortedvalue but can't quite get it to work.

Cheers,

Colin

[JordyWegman]

Hi,

Then this is not possible because both Above() and RangeSum() won't help you. You have to to this in the script.

Jordy

Climber

[colinodonnel]

Hi,

Yes I am coming to that conclusion.

The following doesn't work as I hoped:

alt(sum(value),FirstSortedValue (value, -Monthdate) )

(that the firstsortedvalue function would populate missing cells with the last valid value. Perhaps due to the timing of when these are executed or simple that firstsorted value doesn't operate in this fashion??)

The next thought was to add a field in the script with  a calculated number which could be used in the above function in the chart (the state how many rows to look at). But again, stuck as the number is fixed and doesn't change for the missing/intervening months.

Effectively I am just trying to avoid generating lots of additional rows in the data as the underlying dataset is quite big.

[JordyWegman]

Hi Colin,

Reading this, I can image that you don't want extra load on your data, but adding this column is the easiest way. And after the transformation you can delete the column so this won't result in a lot of extra data. You won't get any extra rows, because it's not generating any extra.

The first solution I gave you would be the best and then add:

Drop column value from Months;

Jordy

Climber 

[colinodonnel]

Hi Jordi,

Yes I think you are right.

There is no alternative except to solve via scripts.

Creating these data points "on the fly" won't work. Via script also means that a lot of the calculations are already done and reduces the burden in the front end by using constructs of the Accumulate Sums on very granular level of detail.

The final solution used "Populating a table with warehouse balances" from here:

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

Thanks for your response,

Cheers,

Colin

[JordyWegman]

Hi Colin,

Good to hear, please mark the post as solved!

Jordy

Climber