Alteryx error data convert

[LEXQ2005]

All data initially are in the form of Date/Month/Year. However, after the data feed into Alteryx, the data format changed. Some maintain the same format, while some changed to the format of YYYY-MM-DD. Every date should be a past day. I found some obvious convert errors, such as:

The Alteryx workflow is simply:

The attached Sample Data Input file is the data to put into Alteryx and Alteryx Data output file is my current Alteryx workflow result.

How can I solve the issue in Alteryx Designer?

[RolandSchubert]

Hi @LEXQ2005,

it seems, your Excel file contains different file formats (dd/mm/yyyy and yyyy-mm-dd). You can use a condition and a DateTimeParse formula to convert. I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

[LEXQ2005]

@RolandSchubert 

I did use a condition and DateTime tool, after that the issue was not solved.

Would you please use the Sample Date Input file I attached in the initial post as the data source when you are looking for a solution?

[T_Willins]

Hi @LEXQ2005,

Alteryx is reading some of the cells as a date and others as text.  When you look at the Excel file you can see Excel is treating some cells differently by right justifying (date format) them.  Alteryx can read these as date fields, but will format the field to the field type that will take all the data in that field:  date and text = string format.  The Auto Field tool will not correct this as the data is still in a mixed format.  There is a simple solution and that is to use two DateTime tools, each looking for one of the two formats, then join the data using a Formula tool. Hopefully the attached solves this for you.

[DawnDuong]

hi @LEXQ2005 

The issue is because you are using the "Autofield" tool which will "guess" when the date is ambiguous. For example, when you have 11/5/2011... it can be either 11-May-2011 or 5-Nov-2011.

If you know for sure the format if your data, the safer option is to use the Datetime Tool which allows you to specifies the format of the incoming string to avoid a 50% chance of making a wrong guess.

Dawn.

[LEXQ2005]

@T_Willins 

The output contains some data that is after today, which is not true, every date should be a past day. 

I have no idea what the JavaScript developer and database admin did to make it so hard to convert, or this issue indicates a shortage in Alteryx. 

At this moment, I have a temporary solution in Excel: Text-to-column -> Concatenate -> convert to date. It works well in the aspect of generating the right output. 

I hope Alteryx can provide an excellent solution to eliminate the manual fix in Excel.