Null value handling based on next available data

[Kai1411]

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!

[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

[Clifford_Coon · Accepted solution]

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

 

[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. 

1. Transpose all the data columns
2. Identify the gaps in each of the columns
3. Find the start/end values and gap length for each gap
4. Calculate the increment for each gap
5. Apply the increment to each row in every gap
6. 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.
7. Dynamically rename the columns

Here are the results

Initial dataInterpolated Data

Dan