community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

DateTime Parse showing incorrect results

Highlighted
Meteoroid

Hello,

 

I have an Excel data set with dates formatted differently within one field. I was trying to use a DateTime Parse tool to parse each of the formats separately (i expected null values for dates formatted differently then the selected match format). However, when I choose the yyyy-MM-dd as the format to be matched in the tool, it also parses a date formatted dd/MM/yyyy (19/12/2018 becomes 2019-12-20 instead of 2018/12/19 or a parse error/null value).

 

Is there any way to stop this from happening?

Or do I have to use a workaround and a conditional DateTimeParse formula for parsing the date? 

 

I would appreciate any support.

 

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @iwonab

 

What is the configuration of the DateTime tool you're using?

 

I'd recommend to use the DateTimeParse formula, I'm pretty sure it returns Null Values as you try different dates.

 

example.PNG

Meteoroid

Hi @Thableaus

 

I'm using the version 2018.2.6.51223 of Alteryx designer. The thing is that when I use the build-in Datetime Parse tool I get the results like this: 

 

Example 1.PNG

 

When I use a formula for the same purpose, the results are the same:

 

Example 2.PNG

 

In both cases I get null for '19.12.2018' because it's not recognised. I was wondering if I can configure the tool to show me nulls for all date formats that are not matching the pattern yyyy-MM-dd. So basically I was expecting to get the same result for 19/12/2018 and 19.12.2018. 

 

I already found a solution. When I use a conditional formula I can get the results I need (and adding additional conditions will parse all of the formats correctly, if needed): 

 

Example 3.PNG

Example 4.PNG

 

 

So my point is I would rather like Alteryx to match the exact incoming date pattern I choose (in this case yyyy-MM-dd) than to "guess" the date based on some other patterns.  I was not able to achieve this using DateTimeParse alone, but I found a workaround.  Thank you for your support!

 

Alteryx Certified Partner
Alteryx Certified Partner

Hey @iwonab

 

Have you tried using only a select Tool, to change your String Field to a Date Field?

 

I can clearly see that your date is already on Alteryx format, so when you use a select Tool to change from String to Date, the Date in the correct format is going to automatically be converted, so no need for parsing.

 

Let me know if it works well for you.


Cheers,

Meteoroid

Hi, 

 

I wanted to follow up on this old topic  

 

What I did is I created a Multi-Field formula, which solved my problem.

 

IF regex_match([_CurrentField_],'\d{4}\-\d{2}\-\d{2}') then datetimeparse([_CurrentField_],'%Y-%m-%d')
ELSEIF regex_match([_CurrentField_],'\d+\-\d{2}\-\d{4}') then datetimeparse ([_CurrentField_],'%d/%m/%Y')
ELSEIF regex_match([_CurrentField_],'\d{4}\/d{2}\/\d{2}') then datetimeparse([_CurrentField_],'%Y/%m/%d')
ELSEIF regex_match([_CurrentField_],'\d+\/\d{2}\/\d{4}') then datetimeparse([_CurrentField_],'%d/%m/%Y')
ELSEIF regex_match([_CurrentField_],'\d{4}\.d{2}\.\d{2}') then datetimeparse([_CurrentField_],'%Y.%m.%d')
ELSEIF regex_match([_CurrentField_],'\d+\.\d{2}\.\d{4}') then datetimeparse([_CurrentField_],'%d.%m.%Y')
ELSEIF regex_match([_CurrentField_],'\d{5}') then datetimeadd('1900-01-01',tonumber([_CurrentField_])-2,'days')
ELSEIF regex_match([_CurrentField_],'\d+\-.+\-\d{4}') then datetimeparse([_CurrentField_],'%d/%b/%Y')
ELSEIF regex_match([_CurrentField_],'\d+\-.+\-\d{4}') then datetimeparse([_CurrentField_],'%d/%b/%Y')

else [_CurrentField_] endif

 

I change all field types to string and then use the Multi-Field Formula to parse the dates, This is because I can have multiple date formats in one column.

I continue adding new rules if necessary.

 

The only thing I am not able to do this way is distinguishing the American date format, as I do not have this information in the original text data source (so I had to assume that 04.05.2019 is the 4th of May not the 5th of April). 

 

Thank you for your support!

Labels