Fill in values based on previous or next non-null values

[SomaSrikanth]

Hello friends! 
I have weekly prices for various products. However, the data is incomplete and there are prices missing for many weeks.
I want to replace the null price records with a value that is 
    1. nearest previous price if one exists
    2. nearest next price if no previous price exists

I am attaching sample input and how I expect the output to look like. 
Is there an easy way to achieve this? I am not familiar with macros. 

[binuacs]

@SomaSrikanth One way of doing this is with the batch macro. Your expected result shows only 842 records but the input file has 4099 records

[Yoshiro_Fujimori]

@SomaSrikanth ,

As your data is too big as a sample, I made a new one.

My idea is to fill the data from Top-down and then Bottom-up.

Workflow

Output

I hope this works for your case.

[SomaSrikanth]

Yoshira_Fujimori, Thanks. I used the Muti-row formula and nested if statements but it works if the data is missing in 2 or 3 or 4 or 5 rows. But in my data, sometimes i have 20-30 missing values and it is a lot of work. 

[SomaSrikanth]

Thanks @binuacs. Sorry for the difference in input and output records. I uploaded a wrong input file (before truncating some products).  
Looks like I need a later version of Alteryx to see your exact workflow.

Thank you. 

[binuacs]

@SomaSrikanth open the .yxmd file in a notepad++ and update the version number ( will be in the right top corner) with your alteryx version , the. The workflow will open 

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-Open-an-Alteryx-Package...

[SomaSrikanth]

@binuacs Thanks a lot for your efforts. I tried your suggestion. I was getting an error message "There was an error opening "xxx.yxmd": Input string was not in correct format." I chaned my Alteryx version from 2022.1.1.40869 to just 2022.1 and it kind of took care of that error message but I still have issues configuring the macro. (Please refer to the 1st image). I created a path for macros and put your macro in the path and brought in the macro into the workflow (image 2) but I am not sure how to configure it.  I would be helpful if you can show me with a screenshot, if possible.

Thank you so much!

[binuacs]

@SomaSrikanth I will send you the workflow and macro separately 

[binuacs]

@SomaSrikanth I thought i didnt send the macro and the workflow, seems to be I sent all the files to you. To resolve the issue save the macro in your folder then in the workflow canvas right click - Insert- Macro - select the macro and connect to the field. Let me know if you see any issues 

[SomaSrikanth]

Actually, it worked when I used the packaged workbook. It did not work when I tried to use individual files for workflow and macro. 
Thanks @binuacs . I will spend some time understanding this and update.