Challenge #93: Pumpkin Production

[jarjarbingie]

Spoiler

[Bluemoon]

Overly complicated my solution, might try and get it down to fewer tools...

Spoiler

[erick_irigoyen]

Solution attached

[jbrowne02]

A lot of joins. Maybe there's a better way?

[jlangl11]

Spoiler

Decided to do it two different ways. One with the sorting attempt used by Patrick and then the way using summarize which was more natural to me.

[SueDonim]

Fun exercise - I'm happy with my solution this time (at least I am before looking at the others)

Spoiler

What I learned:
- ReplaceChar function (for getting rid of pesky commas)
- Dynamic Rename (had used once, but forgot it)
- At least a couple of uses of Sample tool
- Careful design can simplify process (duh!)

Process:
- Remove commas from data
- Padleft State ANSI code
- Convert string to number
- Separate piped data into columns
- Change first record to field names with Dynamic Renaming tool
- Joined data on State ANSI code
- Summary tool to calculate average production by state (Group by State, then calculate average) - really only needed one of these tools rather than two
- Join data together with data in an upper stream and a lower stream
- Sort data by year and by production (descending)
- Sample Top 1 from upper stream (for Max)
- Sample Bottom 1 from lower stream (from Min)
- Cleanup fields
- Calculate difference from average for each stream
- Join data into single table

[Jean-Balteryx]

Here is my solution !

[blundebjerg]

Here is my long-winded but complete solution!