Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Newbie: For any given field value, give difference between other field values

5 - Atom

Hi all--


I'm new to alteryx (it's a great tool), and have a question.  Perhaps there's an easy way to do this, perhaps not--but I haven't been able to figure it out myself.


Suppose I have a datasheet with three fields: Name, Country of Origin, and Age.  I want to transform this into a datasheet that shows, for each country, what the difference between that country's max age and min age is.  For example, if the original datasheet has four entries for Switzerland:


Jane // Switzerland // 42

Doug // Switzerland // 2

Ronald // Switzerland // 11

Eunice // Switzerland // 20


Then the output datasheet would have, as one row: Switzerland // 40 .  This output datasheet should have # rows = # unique Countries of Origin in the input datasheet.


Any thoughts on this?  Many thanks!

14 - Magnetar
14 - Magnetar

Hi @Philly!


This could be accomplished with just a couple of tools in Alteryx.


I would first recommend using a Summarize tool. In the Configuration Window, you will want to GroupBy the [Country of Origin] field and select the Min and Max of the [Age] field. In this window, you can also choose to rename those output fields if you'd like. 




Next, simply add a Formula tool and create a new field with the following expression:


This should get you the output you're looking for. If you want to get rid of the min and max age fields for each country, just add a Select tool and deselect those two fields.



Hope this helps!