Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Linear Interpolation of Values

jbh1128d1
10 - Fireball

I have a data set that I have some values that need to be interpolated values and extrapolating others.  The data is set up as the same:

 

RecordID    Model    Variable     Level    Value

 

The formula to do this in excel is as follows if one value is null:

 

Row-1:Value]+([Row-1:Value]-[Row+1:Value])/([Row-1:RecordID]-[Row+1:RecordID])

 

That works and is verified.

 

However, if there are two null values, or three null values, then you choose the value before and the next value with that value set.  The first question surrounds how to choose the next filled in value and set that?  

 

Example: 

example.JPG

 

The above shows two null values.  If I were to use excel, it would be 656Value+(656Value-659$Value$)/(Row656-Row$659$).  Obviously, the denotes the values would be set.  There are six different variables, and they are their own group.  How can I do this with Alteryx?

 

Data is attached.  

 

 

 

 

4 REPLIES 4
BenMoss
ACE Emeritus
ACE Emeritus

Hi,

 

This was a fun challenge.

 

Here I have built a solution for you to identify the closest non-null value before and after the null record.

 

This should then allow you to apply your formula against the 'Source_Value' column.

 

Either way this should give you a good start.

 

Ben

Philip
12 - Quasar

Here's my solution. The only thing that it calculates incorrectly are nulls with no known values after, but ran out of time to solve it. 

 

Interpolate Nulls.png

PaulDalen
5 - Atom

Ben, I think this will solve a problem I'm having as well. Can you please share the workflow so I can see the formula you used to solve the problem? the file you posted is only the post-processed data.

 

Thanks!

star-stuff
5 - Atom

Thanks Philip, this is exactly what I needed and could not find anywhere else. My data set was also missing values at the end, but decided just to remove this row, always a choice :).

Labels