Error on Alteryx when using a union

[VeronicaElse]

Hello,

The errors I am getting when trying to merge 2 excel docs using a union to append on a continuous monthly basis from one doc that has all the old data and the one that will be the new monthly data that will need to be updated :

Lost information in translation

Invalid date Earliest date supported is Dec 31, 1899

If it says not a valid date time because I have dates/times but it also has NA on some on this same field, what would be the best thing to do next?

Also, for a union when merging two excel docs, I have 2 fields that are not in one of the excel docs, can I still use union?

Thanks.

[Mchappell67]

Hi, @VeronicaElse -

I think you'll need to post some sample data/files.  When I try to input an Excel file with "#N/A" values, they come in as null.

Anyhow, yes - you can join two data sets with differing columns.  The columns missing from dataset will be set to null.

There are options to do this (the default), or abort with an error, or only output the columns that are common amongst all datasets.

Mark

[apathetichell]

Yes - you can use union tool with fields only in one data source - if you tell Union to union all fields. Your date error is probably because there is some data in there that isn't properly formatted date time. you probably need to filter it out or otherwise manipulate it.

[mceleavey · Accepted solution]

@VeronicaElse ,

this is usually because the schema is different, and you are trying to merge two columns that have different data types. For example, in the first file, column A is a date field, and in the second file it's not. This would result in the first file taking the lead on the column formatting and therefore the second input failing.

If you can post some of your files we can help.

M.

[VeronicaElse]

Thanks for all the suggestions

The only problem I have left

Lost information in translation

Invalid date Earliest date supported is Dec 31, 1899

..

any suggestions?

thanks

[VeronicaElse]

I have the conversion error: 281 ----where do you find where this error is? 

then it states this by it too:

Invalid date Earliest date supported is Dec 31, 1899

[mceleavey]

@VeronicaElse ,

you have a date field, formatted as a date, that contains fields that are not in date format.

If you share the data and the workflow we can fix it, but it probably tell you the row number.

M