Replace null Values with previous non-Null value in an array

[SimGiants92]

I have arrays that contain a lot of null values but i would like to replace them not with a single value but instead with whatever previous value in the array is not null, is this possible in DQL?

[krzysztof_hoja · Accepted solution]

Unfortunately we do not have such array function yet, but there is a trick which can be used if you know maximum number of consecutive nulls in your array.

If you can have max 2 nulls in row, query could look like this:

data record(a=array(1.0 ,null, 2.5, 3.0, null, null, 1.1))

| fieldsAdd a = if( isNotNull(a[]), a[], else: arrayMovingMin(a, 2)[] )
| fieldsAdd a = if( isNotNull(a[]), a[], else: arrayMovingMin(a, 2)[] )

 If more, you need to repeat last command more times.

but:

[SimGiants92]

Thanks for the answer, I can't know how many consecutive NULL i will have ahead of time but at least i have an idea of a different solution and i know the function does not exist

[krzysztof_hoja]

Friend showed me this solution not having this limitation:

data record(a=array(1.0 ,null, 2.5, 3.0, null, null, null, 1.1))

| fieldsAdd va=record(a=a[], i=iIndex())
| expand va
| fieldsAdd fixedValue = if(isNotNull(va[a]), va[a], else: arrayLast(arrayRemoveNulls( iCollectArray( if(iIndex() < va[i], a[])))))

| summarize a=collectArray(record(va[i],a=fixedValue))
| fieldsAdd a=arraySort(a, direction:"ascending")
| fieldsAdd a=a[][a]

credits go to @AustinSabel 

 @SimGiants92 , can you tell us more about the need behind this operation? In what situation do you need it. I think there may be a value in having dedicated utility function doing it in one step.  

[SimGiants92]

Hello,
Thanks for the answer!
In my case it is specifically for anomaly detection, i want to trigger an alert if every datapoint is below a certain threshold for an hour. But if some of the values are NULL within that hour the alert does not trigger. I can just assume NULL values are in breach of the threshold by forcing them to be a specific value, but then it gives a graph going up and down (but it still works)