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!
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?
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.
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?
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?
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.
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.
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).
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!