Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Find and Replace Nearest Value

c3reynol
7 - 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

 

 

 

 

9 REPLIES 9
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
c3reynol
7 - Meteor

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
c3reynol
7 - 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?

derekbelyea
12 - Quasar

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

 

2018-01-14_00256.png

c3reynol
7 - 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?

derekbelyea
12 - Quasar

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. 

 

 

 

 

c3reynol
7 - Meteor

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

c3reynol
7 - Meteor

Thanks!  This is good advice.

Labels