cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Find and Replace Nearest Value

SOLVED
Meteor

I need help with a file that has many nulls.  All nulls need to be replaced with a numerical value from the closest date.  I've been able to solve for a single null that has a value above it with a multi-row formula.  However, I need help with a solution for the "1/11/2015" date.  The "FX_RATE" should reflect 0.8431 (I'm rounding for the example).  Thoughts?  Suggestions?  

 

The file is attached.

  

FX_Screenshot.png

 

 

 

 

Alteryx Certified Partner
Alteryx Certified Partner

@c3reynol,

 

If you sort the data in DATE ascending order, you can use a multi-row formula as follows:

 

IIF(IsNull([FX_Rate]),[Row-1:FX_Rate],[FX_Rate])

It fills the Null values for you.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

Meteor

That still leaves me with a null for the 1/11/2015 date.  Can you help with that example?

Alteryx Certified Partner
Alteryx Certified Partner

@c3reynol,

 

Did you build/try the workflow with the multi-row formula?

 

It solves for every row when I tested it.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

Meteor

Yes, I did use a multi-row formula tool.  Since the value for the 1/11/2015 has a null value above it it returns a null.  There is consecutive null values. 

 

For the that date I want it to grab that value closest to it which would be the value for 1/12/2015 date.  Thoughts?

Alteryx Partner

Here are three approaches to the problem. Each one gives a slightly different answer.

 

2018-01-14_00256.png

Meteor

Case 1 and Case 2 would be my desired solutions.  Is there a way to modify their formulas so that it resolves all nulls?  As of now I am left with nulls for "8/17/2015" and "8/23/2015".  Those dates have multiple nulls consecutively.  Thoughts?

Alteryx Partner

I suggest that you start by defining the business rules you want to follow.  For missing values ("NULLS") in a time sequence you can consider these choices:

 

1) Same value as nearest previous non-NULL value (eg. {5, NULL, 10} becomes {5, 5, 10})

2)  Same value as next following non-NULL value (eg. {5, NULL, 10} becomes {5, 10, 10})

3)  Average value using the nearest previous and next following non-NULL values - (eg. {5, NULL, 10} becomes {5, 7.5, 10}  AND  {5, NULL, NULL, 10} becomes {5, 7.5, 7.5, 10})

4)  Interpolation   (e.g.  {5, NULL, NULL, NULL, 25} becomes {5, 10, 15, 20, 25})

5)   Predicted value using linear regression or a time series algorithm based on the known valid values

 

Once your general rule is determined you may want to consider enhancing the general rule and use branching (FILTER tool) when the duration between known values exceeds a defined threshold:

 

e.g.  If the sequence is daily and the values gap to fill is one missing value (one day) then use algorithm X but if the gap is 15 days and there are 15 NULL values in sequence then use a different algorithm Y etc.

 

As part of your solution strategy you may want to first evaluate your data to discover and tag every consecutive sequence of NULL values.  For this data you could use the Interpolation or predictive options referred to above. 

 

 

 

 

Meteor

This solution works when the "Update Existing Field" radio button is selected.

Meteor

Thanks!  This is good advice.