Weekly Challenges
Solve the challenge, share your solution and summit the ranks of our Community!Also available in | Français | Português | Español | 日本語
IDEAS WANTED
Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA
Fun problem, REGEX part at beginning took me a while.
This was a fun one! The victim appears to be Tommy Tutone (or perhaps I should say Jenny)
Spoiler
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
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
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
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
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
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
