Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Help parsing a column to rows and update values with values from parts of the column?

NJT
11 - Bolide

UPDATED: Still trying to figure this out, got pretty close and then hit a scenario I missed... 

Event_DateOrig_Event_Date
OCTOBER 29OCTOBER 29, NOVEMBER 5 & 12
NOVEMBER 5OCTOBER 29, NOVEMBER 5 & 12
OCTOBER 12OCTOBER 29, NOVEMBER 5 & 12
OCTOBER 31OCTOBER 31, NOVEMBER 7, 14 & 21
NOVEMBER 7OCTOBER 31, NOVEMBER 7, 14 & 21
OCTOBER 14OCTOBER 31, NOVEMBER 7, 14 & 21
OCTOBER 21OCTOBER 31, NOVEMBER 7, 14 & 21

 

I missed another scenario to make it more fun.... I know the answer is Regex probably or some combination of formulas and regex, but I'm in a time crunch and trying to resolve this quickly. Hopefully I figure it out before anyone else does and if so I'll post the result. 

 

I have a column where they decided to roll up event dates where the day of the week and the time of the event were the same just the date of the event changed, I need to unroll this data to append attendance information by date and time to the meeting. Time is in a separate column so that's not an issue. The way the formatted the rolled up dates is slight inconsistent which is what has me scratching my head a little. So if the event fell in the same month they just added an & and the number, I need to add the Month to that so when I parse it to new rows I know what month it was in. So far easy enough to parse on the & with a text to columns oh and then I guess I could use a formula tool to look back at the Event_Date field and where the new Date field has just the numeric value I can insert the Month there. The wished for end result is an actual date formatted field. 

 

INPUT                                                                                 Desired Output

Event_Date                                                                         Date

NOVEMBER 19, 26 & DECEMBER 3                                2018-11-19

                                                                                           2018-11-26

                                                                                           2018-12-03  

DECEMBER 1                                                                    2018-12-01 
NOVEMBER 27 & DECEMBER 4                                      2018-11-27

                                                                                           2018-12-04
NOVEMBER 29                                                                  2018-11-29
OCTOBER 18                                                                     2018-10-18  
OCTOBER 18 & NOVEMBER 1                                         2018-10-18

                                                                                            2018-11-01
OCTOBER 20                                                                     2018-10-20
OCTOBER 23 & 30                                                             2018-10-23

                                                                                            2018-10-30

5 REPLIES 5
ddiesel
13 - Pulsar
13 - Pulsar

Hi @NJT !

 

I'm not so sharp at RegEx yet, but here's one way to muscle it into a DateTime format. I think I threw every tool in the kit at this one, so I'd love to see a better solution! 

 

Capture.PNGCapture2.PNG

NJT
11 - Bolide

I'll try yours out @ddiesel it looks better than mine! 

 

Capture.PNG

NJT
11 - Bolide

I should have included an eventID on that sample data, your flow definitely works, now I just have to figure out how to join these dates back to the original data in the larger dataset I'm working on. This is by far the most annoying data set I've had in a while, especially considering when I gave it to them I had cleaned up the event list and added eventIDs to I could match it up later with attendance records and count of prospects mailed to. And in the mail file they sent me back they had rolled up events so dropped the event IDs, and in the attendance file they dropped everything and put all the event location information into one field that I had to parse out and clean up again to get the locations to match the events on the mail file. And with up to 4 events per prospect and up to 4 dates per event at the same time day of the week and location... unraveling all this just to figure out how many prospects were mailed for each event location and how many people attended each event has been a challenge. 

ddiesel
13 - Pulsar
13 - Pulsar

@NJT Ah, the struggle is real when dealing with people that collect data... but don't understand how data might be analyzed. I feel your pain.

 

Capture.PNG

 

If you have a sample dataset with the eventID, I'd be happy to take a stab at it.

NJT
11 - Bolide
The whole project is painful 3 mail files three drop dates up to 16 event dates mapped to a prospect on the mail file. The mail files all overlap plus an extra 50Kish records that are only in one or two of the files. And the formatting of the data that they use for dates is a nightmare that you solved.

I’m just trying to add attendance info to the prospects so I can figure out if meeting location distance or day of week or time of day has any impact to attendance. But the attendance file doesn’t have prospect info just meeting location and attendance count. Plus the attendance file they didn’t include my eventID I assigned when I cleaned it up the first time from their horribly formatted Salesforce data.

The whole thing just drives you nuts!

CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
and privileged information or may otherwise be protected by law. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please contact the sender by reply e-mail
and destroy all copies of the original message and any attachment thereto.
Labels