Date or Tool - Multi-row Formula

BarleyCorn

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 

DateorToolIssueV1.yxmd

MarqueeCrew

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