Alteryx Formula Syntax help

[KeelaWilliams]

Need formula/syntax help for performing the following:
if [columnA] contains a date AND [columnC] contains a value, THEN [columnB] is [columnA] for that row.

[geraldo]

@KeelaWilliams 

Formula:

if !IsNull(DateTimeParse([columna],'%m/%d/%Y')) and IsNumber([columnc]) then [columna]
else Null()
endif

[apathetichell]

assuming your dates are not formatted as dates:

set your formula tool for column b

if regex_match([column a],"\d{2}/d{2}\d{4}") and !isempty([column c]) then [column a] else null endif

if your column a is an actual date:

if !isnull(([column a]) and !isempty([column c] then [column a] else null() endif

the above assumes column c is a string. if it is not - switch to !isnull vs !isempty.

[jdminton]

I added a couple of extra values in column A to test the date piece, but this workflow/formula works. The first formula converts the date for Alteryx to read as a date. The second does the requested test for Column B.

[Clifford_Coon]

Hi @KeelaWilliams ;

Making some assumptions:

1. Column A is a String Field and can contain items besides dates.
   REGEX_Match([Column A], "\d*\/\d{2}\/\d{4}")
2. Column C is also a string fields and can be other items so look for a 12-digit number. 
   REGEX_Match([Column C], "\d{12}")

Then formula is IF [tstDate] AND [tstNumber] THEN [Column A] ELSE [Column B] ENDIF

Happy Solving ;-)

 

[KeelaWilliams]

Attempted to copy solutions and results in correct data but the date formats on the Input source are in mm/dd/yyyy but Alteryx is changing it to yyyy-mm-dd. How do I change the format back to mm/dd/yyyy?

[apathetichell]

Alteryx is telling you that your excel column is already a date. it is being read in as a date natively and you do not need to check if it is a date - only if it is null or not.

in database/Alteryx/everything but excel - a date is YYYY-mm-dd. In excel it's whatever you want it to be - but that's an Excel construct. It's part of vast vlookup brainwashing. There are many visions of a dystopian future wrought by Chat GPT and AI. To me it starts with revising what is and what is not a date and replacing the concept of a join with a concept of a vlookup. This is going to inspire some La Jetee/Chris Marker moment where a programmer goes back in time to see the moment where they inadvertently led to the vlookup abomination.

[jdminton]

Alteryx stores dates in DB format yyyy-mm-dd. When you export to Excel, it will be formatted with the /. If you just want to see it in Alteryx with the slash, you'll need to convert to string and add them with a date/time tool or substring() function. For the date time tool, you would select the option at top that says Date to String