Help With Complex Multi-Row Formula Use Case

Question

Hello, please see the screenshot below. I am trying to fill in the '0' values with the next non-zero value in the same category (Lane). You can see that it works until there are multiple weeks in a row with 0's ('Rate' is the original data, and 'New Rates' is the new field). Is there a way to tell the multi-row formula to replace it only if the value is non-zero? If I could run this tool over and over again until there are no zeros, that could be an option, but I have no experience with macros.  Any thoughts are appreciated.

Luke_C · Answer

Hi @djwitkowski

Just add one more multi-row formula to cover this:

Sample Data.yxmd

djwitkowski · Answer

@Luke_C  Thank you for your reply! This does work, but only if there are no consecutive 0's in the last weeks of the data. I was just using simplified data, and I want to be able to cover all situations where 0's could arise. I attached a more representative sample data set.

The main problem I'm looking to solve is: How can I tell Alteryx to go down the rows until it reaches a non-zero value, then use that value to replace the 0 in the current row? I could sort by values, so that there is never a 0 in the first row, but I also need the weeks to be in order, so that the gaps can be accurately filled in.

Sample Data.yxmd

Luke_C · Answer

Hi @djwitkowski

Here's a different way - I sorted the weeks so the last week is at the top for each lane. Then I used much of the same formula you came up with to fill the 0s from the last week down rather than from the first week down.

New Workflow4.yxmd