Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #136: Cleudo!

Highlighted
8 - Asteroid

Solution attached.

8 - Asteroid

Ozzy_Campos_0-1577537938305.png

Fun problem, REGEX part at beginning took me a while. 

Highlighted
Alteryx Partner

This was a fun one! The victim appears to be Tommy Tutone (or perhaps I should say Jenny)

 

Spoiler
Jaccuse.PNGAnswer.PNG

Process:
Q1: Culprit
- RegEx Match on Message field to find dates (\d{2}\s\w{3}\s\d{2}\s\w{2}\s\d{2}:\d{2})
- Filter for Match = True
- Extract date from Message field
- Convert date to Alteryx format
- Extract time from Message field and convert to Alteryx format
- Combine date and time to datetime format
- Sort meetings by date time
- Text input with body discovery date and time
- Append discovery datetime to data
- Filter out meetings after body discovery
- Select final record (as meeting most recent prior to discovery)
- Convert MessageFrom (phone number) to string
- Find and Replace to lookup person (Plum) with that phone number

Part 1 Solution.PNG

Q2: Weapon
- Split Field 1 to 15 columns
- Dynamic Rename first record
- Append Q1 results to Credit card data
- Filter where Cardholder name equals culprit
- Convert Price to float (not needed)
- Strip out last 4 digits of Credit card number
lower thread....
- Strip out last 4 digits of Credit card number
- Auto-field
- Join threads on Store and credit card last 4
- Text input with 6 possible weapons
- Append weapons to credit card transactions
- Filter for Item string that contains Weapon name
- Clean-up

Part 2 Solution.PNG

Q3: Location
- Create spatial point for discovery location and Instagram locations
- Calculate 1km trade area around discovery location
- Spatial match to determine Instagram locations in trade area
- Clean-up
- Append location to culprit and weapon

Part 3 Solution.PNG 

  

Highlighted
8 - Asteroid
 
Highlighted
8 - Asteroid

My Effort

 

Spoiler
JamesCameron_0-1580144328137.png

 

JamesCameron_1-1580144353828.png
Highlighted
8 - Asteroid
 
Highlighted
8 - Asteroid

This was the most fun challenge and a good exercise in integrating multiple tools. I may have done some of the parsing the long-winded way but it worked at the end. 

Highlighted
6 - Meteoroid
Spoiler
Spolier Cleudo.PNG
Highlighted
8 - Asteroid

A very enjoyable challenge

 

Spoiler
image.pngimage.png
Highlighted
Alteryx Partner
Spoiler
AWC136.PNG

Really fun challenge!