Semi-recursive multi-row question for all you Alteryx mavens

[danilang]

Hi

I'm trying to reproduce the logic that can be done in Excel with just 2 simple formulas.   

The table is this

Basically Computed WL is

if not isnull([Measured WL]) then
    [Measured WL]
else
    [Row-1:Freeboard]
endif

And Freeboard is

    [Measured WL]*0.95

It's only Semi-Recursive because the input to one row is the output of the previous one.  This works in Excel, because Excel ultimately calculates the entire row before proceeding to the next one.

In Alteryx we find ourselves with following situation

 

This work flow produces this result

Where the Measured WL is null, the Computed WL should be the Freeboard from the row above, like the Excel sheet.

I'm looking for a way to ensure that 2 tools are executed in sequence for each row of data before moving on to the next row

It seems that this "should" be simple to reproduce with built-in tools, but i'm stumped.  This may be solved using a macro, but i'm not familiar enough with them to know how to pass in not just the current row, but also the row before.

Any ideas?  

P.S. I've also attached the workflow(such as it is) and the excel file

Edit:  In talking this over with one of my colleagues, i realized that this simple example can be solved by calculating the Freeboard column first.  However, in my real world data, there is no single column that can be calculated before all the others.   I still need a way to process the 2 tools for each row before proceeding to the next

Thanks

Dan

[danilang]

Sorry @CharlieS

The original thread went missing.  I added your suggestion about the field types and and uploaded a new workflow.  The old one had the incorrect field names in the tools and didn't correspond to my description of the problem

Any thoughts on how to fix that?

I Starred your original reply.  Did you manage to see it?

Dan

[CharlieS]

Did you see my attached solution from the original reply?

[mmongeon]

I've struggled with similar cases in the past.

What I've done before is to figure out if anything can be calculated first, and then follow through with the other columns.

In your case, the Freeboard can be calculated with a Multi-Row Formula, and then a simple Formula gives you the Calculated WL.

I know this may not be the answer you're looking for, it may not be transferrable to your larger data prep scenario.

But it does solve the example you submitted.

[danilang]

hi @CharlieS

Thanks for your reply yesterday.  I incorporated your select tool to get the values as doubles. 

As I mentioned in the update post

Edit:  In talking this over with one of my colleagues, i realized that this simple example can be solved by calculating the Freeboard column first.  However, in my real world data, there is no single column that can be calculated before all the others.   I still need a way to process the 2 tools for each row before proceeding to the next.   

Your work flow does the equivalent by taking the calculation from Freeboard and putting it directly into Computed WL. 

Note:  I also updated the sample work flow to actually calculate the correct columns in each tool. The one I originally updated was an older version

Dan

[danilang · Accepted solution]

Solved it.  Transposing the field coupled with a multi-row formula based on field names was the answer

Doing all those weekly challenges paid off

Kudo's to @CharlieS for helping me out with the initial data types and to @mmongeon for running through possibles with me