Replace nulls within ordered data

Hi everyone, I've searched the forums but I can't find a solution to this problem.

I have an ordered, evenly spaced list of Intervals. Some of the values are known and some are null.

For those that are null, I'd like to replace the null with the straight-line point between the two closest known values.

So if I have 1 _ _ 4 then i'd like to get 1 2 3 4 as my result etc.

I've explored using multi-row formulas to find the difference from the closest lower & greater known values but I can't get to where I need.

Any help greatly appreciated.

p.s there's no relationship between Interval & Value (so you can't just set Value equal to Interval).

Datasets

Developer Tools

Output

Tips and Tricks

Common Use Cases

Best Practices

Transformation

Preparation

Data Investigation

Accepted answers

ArtApa

Hi @TSP - Here is a possible solution:

ReplaceNullAA.yxmd

All comments

JosephSerpis

Hi @TSP you can use the Multi-Row tool to solve that challenge

TSP

Thanks @JosephSerpis are you able to share how?

JosephSerpis

Hi @TSP I mocked an example that produces the results you described.

Multi_Row_29112022.yxmd

TSP

Thanks @JosephSerpis unfortunately this assumes there's always a value change of 1 between each interval which isn't the case.

The process needs to find the variance and number of intervals for each null to work out the incremental change.

e.g 5 _ _ 20 _ _ 23

Should return 5 10 15 20 21 22 23.

Many thanks,

ArtApa

Hi @TSP - Here is a possible solution:

ReplaceNullAA.yxmd

TSP

Thanks @ArtApa this is almost identical to the solution I eventually got to.

I re-sorted my data based on an initial RecordID but otherwise the same approach.

Many thanks,

TSP

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703