Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
New content is available in Academy! You may need to clear your browser cache for an optimal viewing experience

Challenge #136: Cleudo!

Highlighted
Asteroid

Solution attached.

Highlighted
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
Asteroid
 
Highlighted
Asteroid

My Effort

 

Spoiler
JamesCameron_0-1580144328137.png

 

JamesCameron_1-1580144353828.png
Highlighted
Asteroid
 
Highlighted
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
Meteoroid
Spoiler
Spolier Cleudo.PNG