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.
Solved! Go to Solution.
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
That still leaves me with a null for the 1/11/2015 date. Can you help with that example?
Did you build/try the workflow with the multi-row formula?
It solves for every row when I tested it.
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?
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?
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.
This solution works when the "Update Existing Field" radio button is selected.
Thanks! This is good advice.