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

How to use Max()

Asteroid

My data returns a row for every page in a stack of scaning.  I want to filter out and keep only the row which is the MAX of the page number values

 

Pat ID | Doc ID | Doc Type | Page Number

001 | 3003 | Lab Results | 01

001 | 3003 | Lab Results | 02

001 | 3003 | Lab Results | 03

001 | 3003 | Lab Results | 04

 

 

So from the example data above I would want to only return the Page Number 04 row.

 

I think I would use the filter tool, but how would I write the custom Max()?

 

 

 

 

Alteryx Certified Partner
Alteryx Certified Partner

You can use a summarize tool:

 

GroupBy - Pat ID

GroupBy - Doc ID

GroupBy - Doc Type

Max - Page Number

 

That will only return that last record.

 

Note:  You will want to rename the field from MAX_Page Number back to Page Number.

 

:)

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted

I would probably do it the same way as @MarqueeCrew, but another suggestion is to use a sort tool to order the results and then a sample tool and group by PatID, DocID, DocType and take the last value.

2016-04-18_21-07-09.jpg

 

Simple sample attached

Asteroid

Interesting.

I easily did this in Tableau with an LOD calculation. 

{ FIXED [Doc ID] : MAX([Page]) }

 

So I thought I'd be able to write an Alteryx Filter using the Max() function?  I guess I might need to build a Macro to cycle through?

 

But to your suggestion - I've not used the Summarize tool to 'filter' data. How do you output from Summarize?  I guess I'll just have to give it a try.

 

If I get it working I'll reply again.  Thanks.

 

 

The Max function is to pcik the maximum value in a single row.

 

If you take the output of the summarise tool into a join tool you can join with the Doc ID and Page Number equal to the Doc ID and Max Page Number. Then you just need to take the Joined output. Hope that makes sense!

Asteroid

Sweet!  I've got lots of uses for Summarizes and take the output.  Lots of uses for this.  Thanks.

Alteryx
Alteryx

Hi Brenda

Per our conversation at the Denver workshop, here is the workflow we looked at.

Asteroid

Thank - I've learned two ways to do this. 

Meteor

Hello,

 

I was trying to use summarize tool to something similar.

The problem is - I can get the Max of Sales as a single value 

but the moment I add City as GroupBy the logic does not hold true.

In short, I was unable to replicate what was suggested.

 

Don't know where I am going wrong. 

The end result I want is to show the 'City'  corresponding to the maximum 'Sales'.

 

sel_max_trbl.PNG

Meteor

So I did find a way of doing it.

I took the max of Sales,

appended it to the original table and

then used a filter to get the entire where Max column equals Sales.

 

But not really satisfied with this method, hope to figure out why I am unable to 

use either of the two solutions listed above.image.png

 

Labels