Null value handling based on next available data

Hi, I'm wondering if there's a formula/tool to handle these types of null values

If a value is null, find the next non-null value, subtracted by the earlier non-null value (every row begins with 0) and divide it by the number of null rows that appear within the column

I tried making use of the multi-field formula but couldn't figure out how to make reference to the next/previous non-null value

Thank you in advance!

Preparation

Accepted answers

Clifford_Coon

Hi @Kai1411 , the multi Row formula tool can look ahead multiple rows, but you would need to create a big formula.

here is for four nulls:

IF !IsNull([Field4]) THEN [Field4]

ELSEIF !IsNull([Row-1:Field4]) AND !isnull([Row+1:Field4]) THEN [Row-1:Field4] + (([Row+1:Field4] - [Row-1:Field4]) / 2)

ELSEIF !IsNull([Row-1:Field4]) AND !isnull([Row+2:Field4]) THEN [Row-1:Field4] + (([Row+2:Field4] - [Row-1:Field4]) / 3)

ELSEIF !IsNull([Row-1:Field4]) AND !isnull([Row+3:Field4]) THEN [Row-1:Field4] + (([Row+3:Field4] - [Row-1:Field4]) / 4)

ELSEIF !IsNull([Row-1:Field4]) AND !isnull([Row+4:Field4]) THEN [Row-1:Field4] + (([Row+4:Field4] - [Row-1:Field4]) / 5)

ELSE null() ENDIF

Null_Values.yxmd

All comments

RSreeSurya

Sorry , my bad, my understanding of the problem statement was wrong, please ignore

Hi,

I have implemented as per the problem statement, please find the attached workflow.

Formula to find previous/Next Null value : IIF(ISNULL([1]),[Row-1:Previous Available Non-Null],[1])

Using Sort and RecordID, I have utilized the above formula to derive both next available non-null value and previous available non-null value.

After these two are derived Formula implemented is:

if isnull([1]) then ([Next Available Non-Null]-[Previous Available Non-Null])/[CountNull_1] else [1] endif

Attaching sample Output:

1 - input column

Expected Final Output - Is the desired output

Null value handling.yxmd

Clifford_Coon

Hi @Kai1411 , the multi Row formula tool can look ahead multiple rows, but you would need to create a big formula.

here is for four nulls:

IF !IsNull([Field4]) THEN [Field4]

ELSEIF !IsNull([Row-1:Field4]) AND !isnull([Row+1:Field4]) THEN [Row-1:Field4] + (([Row+1:Field4] - [Row-1:Field4]) / 2)

ELSEIF !IsNull([Row-1:Field4]) AND !isnull([Row+2:Field4]) THEN [Row-1:Field4] + (([Row+2:Field4] - [Row-1:Field4]) / 3)

ELSEIF !IsNull([Row-1:Field4]) AND !isnull([Row+3:Field4]) THEN [Row-1:Field4] + (([Row+3:Field4] - [Row-1:Field4]) / 4)

ELSEIF !IsNull([Row-1:Field4]) AND !isnull([Row+4:Field4]) THEN [Row-1:Field4] + (([Row+4:Field4] - [Row-1:Field4]) / 5)

ELSE null() ENDIF

Null_Values.yxmd

danilang

Hi @Kai1411

Here's a dynamic workflow that uses a linear interpolation to fill in any gaps in any of the data columns.

It's big, but the basic process is this.

Transpose all the data columns
Identify the gaps in each of the columns
Find the start/end values and gap length for each gap
Calculate the increment for each gap
Apply the increment to each row in every gap
Cross tab the data back to it's original form. This done using the columnID as opposed to the column name, since the Cross Tab tool standardizes columns names and sometimes sorts the columns alphabetically.
Dynamically rename the columns

Here are the results

Initial dataInterpolated Data

Dan

LinearInterpolationGapFiller.yxmd

kgendreau

Thank you @danilang as this was helpful for my use case!

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235