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

Date or Tool - Multi-row Formula

BarleyCorn
8 - Asteroid

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 

4 REPLIES 4
MarqueeCrew
20 - Arcturus
20 - Arcturus

@BarleyCorn,

 

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
BarleyCorn
8 - Asteroid

Hey Mark,

 

Thanks for getting back to me.

Ideally I'd like the entire client name, so not just Joe, but Joe Soap Inc.?

Attached is excel.

 

Also, Alteryx Multi-row tool is now working when I save file as excel 1997-2003 but not most recent excel - very strange??

 

Thanks

 

Tom

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

Resolution:

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

 

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
BarleyCorn
8 - Asteroid

Great work Mark - works like a dream!

 

(BTW, is that a Leprechaun I see as your logo!)

 

Tom

 

Labels