topic Re: Replace null Values with previous non-Null value in an array in DQL

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

SimGiants92 — Wed, 23 Apr 2025 19:08:59 GMT

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?

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

krzysztof_hoja — Wed, 23 Apr 2025 20:53:45 GMT

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:

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

SimGiants92 — Thu, 24 Apr 2025 13:30:03 GMT

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

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

krzysztof_hoja — Fri, 25 Apr 2025 18:46:52 GMT

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.

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

SimGiants92 — Fri, 25 Apr 2025 18:52:43 GMT

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)