Alteryx Designer Discussions

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

Date Conversion Error

SH_94
9 - Comet

Dear Community,

 

I encountered the conversion error on the date format which consist of following date format: 

SH_94_0-1625026863817.png

Error in Alteryx 

SH_94_1-1625026931058.png

 

Below is the format used to convert the date format accordingly.

SH_94_2-1625026987830.png

 

Formula in the text format :

IF !IsNull(DateTimeParse([Invoice Date],"%d/%m/%y")) 
// Check whether it falls in the date format
 
THEN DateTimeParse([Invoice Date],"%d/%m/%y") 
// If yes then parse it with a new format
 
ELSEIF 
!IsNull(DateTimeParse([Invoice Date],"%d-%b-%y")) THEN DateTimeParse([Invoice Date],"%d-%b-%y")
 
ELSEIF 
!IsNull(DateTimeParse([Invoice Date],"%Y-%m-%d")) THEN DateTimeParse([Invoice Date],"%Y-%m-%d")
 
ELSEIF 
!IsNull(DateTimeParse([Invoice Date],"%Y-%m-%d")) THEN ToDate(DateTimeParse([Invoice Date],"%Y-%m-%d"))
 
ELSE Null() ENDIF // Returns null if its a new format
 
 
 
Could anyone enlighten me on which part that i have made the error and how to correct it?
 
 
Many thanks in advance

 

9 REPLIES 9
apathetichell
16 - Nebula

I think you are trying to convert across multiple types of dates in the same column - but your datetimeparse() optionality is just creating errors. The true/false checking shouldn't use datetimeparse since it's not creating a null per se -it's creating an error. The better way to do this is with a regex_match function - and I'll upload a workflow to show you how to do this in a few minutes...

 

Ignore the yucky error messages... I think it goes through the values for each part of the switch but only runs the relevant section... regex wasn't going to work because it can't tell the difference between 19/11/26 and 11/26/19  - at least not easily...

 

Notes - your integer is off by two days according to Alteryx. I cut down your format so the switch was clearer.

DawnDuong
12 - Quasar
12 - Quasar

Hi @SH_94 

you have multiple date formats in your data. So you need to identify all the possible formats, assign each record to a format type and then use a switch or other method to ensure that different conversion applies to different format types 

dawn 

SH_94
9 - Comet

Dear @apathetichell ,

 

May i know if currently there is no format column, how will you write the formula? Could you please guide me on this?

 

 

Many thanks in advance.

DawnDuong
12 - Quasar
12 - Quasar

Hi @SH_94 

you can concatenate the known formats like what is shown in the screenshot here (i only included  3 types - leaving the rest for you to try. Can check out the link here for the syntax and specifiers https://help.alteryx.com/current/designer/datetime-functions).

The warnings are not errors and the results are still correct.

image.jpg

 

apathetichell
16 - Nebula

Did you check the workflow I posted? I used a switch command (after cleaning your format identifier column) with the various date formats.

 

@DawnDuong - Since OP has dates with the same character arrangement and same punctuation representing different date units (ie 11/26/19 and 19/11/26) I do not believe that they can use this data without a format key.

DawnDuong
12 - Quasar
12 - Quasar

hi @apathetichell 

True, indeed. Must use the format key field that is provided there and probably use a switch ha.

Cheers,
Dawn.

SH_94
9 - Comet

Dear @apathetichell ,

 

Thanks a lot for the prompt response.

 

May i know what the switch function works in this case?

 

 

Thank you.

DawnDuong
12 - Quasar
12 - Quasar

Hi @SH_94 

the switch function syntax is explained here:

 

https://help.alteryx.com/current/designer/conditional-functions

apathetichell
16 - Nebula

switch function takes in a value (in this case the format code "a"-"h")  and then provides a value/formula based upon that code (in this case different date/time parses)... Switch is key to simplifying complex nested if/then statements...

Labels