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

[Philly]

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!

[Kenda · Accepted solution]

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:

[Max_Age]-[Min_Age]

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!