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

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.

Sample Input & Output.yxmd

Help

Accepted answers

All comments

binu_acs

@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

Sample Input & Output.yxzp

Sample Input & Output.yxmd

BatchMacro1.yxmc

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.

Sample Input & Output.yxmd

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.

binu_acs

@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-within-Alteryx-version-2019-1/td-p/733055

SomaSrikanth

@binu_acs 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!

Macros Error.JPG

binu_acs

@SomaSrikanth I will send you the workflow and macro separately

binu_acs

@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

BatchMacro1.yxmc

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 @binu_acs . I will spend some time understanding this and update.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280