Hi Everyone,
Hoping this awesome community can help with this issue. I tried different suggestions I've found, but still haven't been able to find the right solution.
We have multiple tables which we are unioning, however, 1 table has a time field with values such as "00:01:38" which is causing the error. (I know that having mixed data types in a column is not common practice, however, the business is requiring these values to be in the same column.)
I have tried parsing out the values into 3 columns, formatting these columns as string and then concatenating them back together. All works fine... UNTIL I add the ":" back in. 😑 This is where it breaks at the Union.
Number and % value work fine which perplexes me since the "%" is a character such as the ":"
Any advice on how to get around this?
Thank you!
Can you enlighten us a bit more about the problem here.
better with an input and desired output.
When you say "which is causing the error.", can you elborate what is the error in what situation?
Thank you for your reply!
After creating the table and testing in SQL with the unions, this is the error message I get:
"Error converting data type varchar to float." -- I believe this is because of the format of "TimeofCall".
When I don't include the ":" it unions perfectly. But I need to include them somehow.
Thank you.
@LIEZL
Maybe you can include a CAST and CONVERT function in SQL Query to convert the data type from fload to varchar before union?
Hi @LIEZL
The error message that you're receiving comes from SQL Server. The only type of column in SQL that will accept all the values that you're passing in is a string type char, varchar, etc.
Dan
Thank you @Qui... I think it might have to come down to that. But my source is from an Excel file so I'll see how I can do that.
We are creating tables from these Excel sources and then Unioning them. I've suggested to the business that we can parse it out so it reads "3min 30 secs" but they are not keen to that idea. I really appreciate your time.
Hi @danilang ,
Yes that is the error from SQL as I was testing the tables after they were created to see if the union would work after trying to fix the field type in Alterxy.
Hi everyone,
I've found a resolution with the business where the value will be converted to seconds. I found this formula online to use and this works when I try to union this data with the existing data.
DateTimeHour([Actual]) * 3600 + DateTimeMinutes([Actual]) * 60 + DateTimeSeconds([Actual])
Thank you again for your time! Cheers!