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.
@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
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.
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.
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.
@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
@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!
@SomaSrikanth I will send you the workflow and macro separately
@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
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.