community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Highlighted
Quasar

Hi

 

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

 

The table is this

 

Formulas.pngTable.png

 

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

 Workflow.png

 

This work flow produces this result

 

Results.png

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

Quasar

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

Alteryx Certified Partner

Did you see my attached solution from the original reply?

 

 

Asteroid

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.

 

Capture.JPG

 

Quasar

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

Quasar

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

Solution.png

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

Labels