Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Auto Field Tool not converting Date Time in %Y-%m-%d %H:%M:%S

cstouwie
7 - Meteor

I’m running into an issue when using the auto field tool in Alteryx.

 

I’m passing different columns of data into the auto field after using a multi-field tool that finds date & time fields then uses dateparse function to put into %Y-%m-%d %H:%M:%S. For some reason, some columns are not recognized by the auto field tool as a date-time field even though they are all in that same %Y-%m-%d %H:%M:%S format (while others are converted without issue).

Any thoughts on why some fields would not be recognized as a date-time by the auto field tool?

 

I attached the contents of one of the columns that doesn't get converted. At first, I thought maybe it was the 00:00:00 so I created a formula to add 01:01:01 and random digits, but that didn’t fix it. Not sure what else would work.

 

Any help would be great!

9 REPLIES 9
apathetichell
18 - Pollux

auto field only works with strings - no? Can you pass a screengrab of a select tool prior to passing to an auto-field tool? I don't see an issue with turning your data into date/times -  but the new datecolumn won't be affected by an auto field.

 

I'm also converting it back to text via tostring() and not seeing an issue. So are you using a select tool to convert instead of tostring, or perhaps is there string column which is tiny and not-large enough for the full date?

Emil_Kos
17 - Castor
17 - Castor

Hi @cstouwie,

 

Not sure if I understand your request correctly but it looks like that works for me. 

 

Emil_Kos_1-1622667704283.png

 

cstouwie
7 - Meteor

Here is a screengrab of the fields incoming to the auto field tool. All are strings and all date-time fields are the same and you can see on the output meta that some are converted to date-time fields while others are string 19.

 

cstouwie_0-1622667833217.png

cstouwie_1-1622667900920.png

 

cstouwie
7 - Meteor

I'm not removing Nulls and actually need to keep them. Could that be my issue? Do the blanks/nulls need to be in a different format?

Emil_Kos
17 - Castor
17 - Castor

Hi @cstouwie,

 

Actually, it works for me with and without nulls.

 

I think there is some issue with the rest of the data.

 

Maybe you can just use the date-time field tool to search if any of the records will not throw an error? 

apathetichell
18 - Pollux

Createddate looks like it's the only one which was ever converted to Datetime... are you sure part of your workflow is supposed to convert DueDateTime to date/time? I'm not seeing any errors with this. Note - on what you posted I had to take fieldname from firstrow of data. If you haven't already done so there could be text intermingled with the actual dates.

 

 

cstouwie
7 - Meteor

Unfortunately, that is not an option for me in this workflow.

 

I have the auto field part of a macro that converts singular JSON column coming from an API by pivoting that column into 30+ different tables (cross tab tool) with each table having different field names. I was hoping to be able to automatically detect which fields were date-time (after using the nice multi-field date-time parse function so they are all the same format) while also configuring other field formats without having to configure what the field name is (like in the date-time tool) using this auto field. 

 

It's a bit frustrating because this use case is exactly what the auto field is meant for but it bugs out on some columns but works smoothly on others even though they are all the exact same %Y-%m-%d %H:%M:%S format.

 

P.S. I also looked at the fields that are converted successfully actually have more nulls than the one i provided, so that is not the smoking gun.

 

 

 

cstouwie
7 - Meteor

@apathetichell :

Createddate is the only you can see in this screen shot but there are a whole group of tables coming out of this macro and it seems to be about 50/50 on whether it catches it or not. In that table there are 5 date-time columns all in the same format and 2 don't get converted while the other 3 do. That is just one out of the 30+ tables coming out of the batch macro in which I'm seeing the same thing happen (some are converted to date-time others are sting 19).

cstouwie
7 - Meteor

This may help you understand. See attached input and output data with just the date-time fields. Again this is just one of my tables so if you want more of these instances on other tables with different field names I can provide them.

Again, I wish I could use some type of date-time conversion tool but my field names are dynamic and may be changing in the future out of my control.

 

Thanks in advance for looking into it!

Labels