Challenge #138: World Series Wins

[SueDonim]

Struggled a bit, but learned quite a lot about data manipulation, and a little about RegEx

Spoiler

Bounced around with RegEx a bit, and learned some, but ultimately decided that Left functions would be easier given the different types of noise in the team data

Process:
- Cleaned up winning team and losing team data, but searching for "(" and extracting all to the left
- Repeated above by searching for "["
- Split Winning Team and Losing Team through two select tools
- Combined all teams (stacked the two) with Union tool
- 3 streams for calculations
  -- Count number of appearances per team (Grouped combined team list by team and counted instances)
  -- Count number of wins per team (Grouped winning team list by team and counted instances)
  -- Find and Replace tool to combine the columns of All teams, Count of appearances, and Count of wins
     --- Calculated ratio of wins to appearances
     --- Reformatted result to string: multiplied by 100, rounded to integer, converted to string and appended "%" (also had to replace "%" to 0% where team never won (i.e. wins was Null)
  -- Concatenated winning years (Grouped combined team list by team and concatenated years
- Find and Replace tool to combine all data
- Select tool to remove unneeded fields and rename for output

[DBourke19841]

This took me an age to figure out cleansing the team names - need to get on the Regex tool mastery sessions.

Spoiler

[justindavis]

Pesky whitespace character coming out of my regex until I utilized \b to rid myself of it. Not my fastest, by a long shot.

Spoiler

[blundebjerg]

Here is my solution

[Snare]

Found this challenge and, with the start of the 2019 World Series, I thought this would be a good challenge for me to visit. I added in Boston's win over the L.A. in 2018. Looking forward to adding in the Washington/Houston outcome at the end of October.

[gracec97]

Spoiler

[mat_budden]

Hi All,

Please find my Result below.

Spoiler

Split the Teams from Winning and Losing into Formula to get the Wins and rejoined with Union, Then Filtered, Into Select, 2 Summaries then Join Back and Carry the left, Cleanse, at the Percent, Select and done... Probably not the most efficient, But it works.

Thanks

Mat

[Borja_Leiva]

My solution