Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to use Max()

brendafos
10 - Fireball

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()?

 

 

 

 

11 REPLIES 11
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
jdunkerley79
ACE Emeritus
ACE Emeritus

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

brendafos
10 - Fireball

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.

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

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!

brendafos
10 - Fireball

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

MikeN
Alteryx Alumni (Retired)

Hi Brenda

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

brendafos
10 - Fireball

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

pushkardps
7 - 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

pushkardps
7 - 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