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()?
Solved! Go to Solution.
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.
:)
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.
Simple sample attached
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!
Sweet! I've got lots of uses for Summarizes and take the output. Lots of uses for this. Thanks.
Thank - I've learned two ways to do this.
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'.
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.