Hello,
Funny one here. Using a Reg Ex and Multi-row tools to make a new colunm with clients name on each row.
Attached is WF, The Multi-row tool should be creating a new column with just client names from column one. File inputs are the same, data format in excel is same. But when inputting into Alteryx, some files go in with dd/mm/yyyy format and Multi-row works, other files (same excel format) enter Alteryx as yyyy/mm/dd and multi-row then does not work?
Any ideas my Alterys Brothers?
Thanks
BarleyCorn
Solved! Go to Solution.
Unfortunately, when you posted the workflow you did not export the package or post the input limericktest.xlsx. Your tokenize did catch my eye it looks like you are trying to output the first word. Is that correct?
GetWord([Phd],0) will get you the first word from the field, Phd.
Maybe in the morning you can post the file and I can take a look at it for you. Will you be at inspireEurope2017?
Cheers,
Mark
With an excel formatted worksheet we needed to use a "Phd" column to determine the client name. The first client name appears in A1 and the rest of the client names appear when the prior row has a value of Page in it.
IF isempty([Row-1:ClientName]) THEN [PHd] ELSEIF LEFT([Row-1:PHd],4) == 'Page' THEN [PHd] ELSE [Row-1:ClientName] ENDIF
Next we want to keep rows where a date is present in the 1st column (Phd). We used a filter looking for dates:
regex_match([PHd],"\d+-\d+-\d+")
After transposing the data, we want to eliminate all empty values. This is accomplished with a filter (Value is Not Null)
Next we convert all "Values" to time (HH:MM:SS) where :SS are always set to "00". I used regex for this, but you could use string functions since we standardize all incoming times to be padded with a leading zero:
regex_replace(padleft([Value],4,"0"),"(\d{2})(\d{2})",'$1:$2:00') /* first group of time is (2 digits) second group of time is (2 digits) concatenate the first group plus a colon followed by the second group plus a colon plus 00 */
Tom,
It was a pleasure.
Cheers,
Mark
Great work Mark - works like a dream!
(BTW, is that a Leprechaun I see as your logo!)
Tom