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 |
解決済! 解決策の投稿を見る。
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.
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...
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
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
Amazing!!! This worked great! I have to learn more about parsing with regex formulas.
Thanks a lot Esther!