cleaner way of many formulas / if statements for column parsing

[hamzam]

Hello,

I have a column like the one below. I would like to get individual columns: Monday,  Tues, Wed, Th, Fri, each filled in with their respective times.

For example, the first row will have additional columns with Mon = 7am-16pm, Tuesday = 7m-16pm,  same for Wed,Th,Fri. (not all are M-F, which stands for Monday to Friday)

I guess I could write bunch of formulas brute force, but wanted to know if there is a better way to parse it.

Times

M-F07:30A-16:00P

M-F07:30A-16:00P

M-F07:30A-16:00P

M-F07:30A-16:00P

M-F07:30A-16:00P

M-F07:30A-16:00P

M-F07:30A-16:00P

M-F07:30A-16:00P

U8A-16P,M-TH7:30A-16:00P

U-TH00:00A-08:00A

U-TH00:00A-08:00A

M-F07:30A-16:00P

M-F06:00A-14:00P

M-F07:30A-16:00P

M-F15:00P-23:00P

M-F07:30A-16:00P

M-F07:30A-16:00P

M-F07:30A-16:00P

T-F7:30A-16P,S8A-16P

[atcodedog05]

Hi @hamzam 

Can you tell what does this mean?

U8A-16P,M-TH7:30A-16:00P

And list out the Notations for each day of the week.

[hamzam]

hello,

U = sunday.

Sunday: 8A-16P

Monday 7:30A-16:00P

Tuesday 7:30A-16:00P

Wednesday 7:30A-16:00P

Thursday 7:30A-16:00P

Friday: empty...

[estherb47 · Accepted solution]

Hi @hamzam 

Try this out: 

First. assign a row id to keep rows distinct from one another. Then parse out rows with comma separated dates/times. 

I've parsed the starting weekday, ending weekday, and time range with regex replace formulae in a formula tool. A lookup table, assigning a number to each weekday, is then used to assign numbers to the starting and ending days. And a generate rows tool generates all of the interim days. The days are looped back in through that same lookup table.

No need for brute force formulas. Alteryx makes it easier.

Let me know if that helps!

Cheers,
Esther

[estherb47]

And. if you want that across in a table instead of down in rows, a crosstab tool, using the Record ID as row headers, Weekday as the column headers, and Time as the Value (concatenated) will make that happen.

Cheers!

Esther

[hamzam]

Amazing!!! This worked great! I have to learn more about parsing with regex formulas.

Thanks a lot Esther!