I have an external table in redshift and I’m connecting it to QS using Direct Query as the dataset is very large. I have a string column that has date in the format yyyy-MM-ddThh:mm:ss.SSSZ. When I use it in the visual, the visual fails with the error mentioned in the title.
So, I tried converting it to a date column in QS using “Change datatype” in Edit Dataset console. I get an error saying We encountered an error trying to validate the date format. Please try again. I tried this solution which should ideally work, but it doesn’t. It says the same error.
Next, I created a calculated field by splitting the string column with ‘T’ and converting the first position to date using QS parseDate. Like this parseDate(split({accounting_date}, 'T', 1), 'YYYY-mm-DD'). This works fine in “Edit Dateset” console, but when I use it in visual, I get the same error again Your function expression contains unsupported data.
So, I thought to move these calculations to Redshift and use this query instead normal of select * query.
SELECT
*,
TO_TIMESTAMP(SPLIT_PART(accounting_date, 'T', 1), 'yyyy-MM-dd') as timestamp_new,
TO_DATE(SPLIT_PART(accounting_date, 'T', 1), 'yyyy-MM-dd') as date_new
FROM
{table}
This query works fine in both Redshift console and in “Edit Dataset” console. I’m able to see them like this.
This might be related to some data that might not be able to be converted correctly from your database. One thing to try is to import the data to SPICE and see if you get any errors on the import that can point you in the right direction.
Hello @iamtarun, did the response provided by @andres007 help guide you towards your expected output? If so, please mark their response as the solution. Otherwise, if you have further questions on this topic, please follow-up with more information and we can guide you further. Thank you!
Thank you for your response. I’m sure why but, creating a new dataset with the same query worked fine.
The only difference is that the initial dataset was actually SPICE. The data was becoming too large, so I had to move to Direct Query. So, I changed it to Direct Query in “Edit Dataset” page and since then I had this issue. Creating a new dataset with Direct Query worked fine.
Not sure if moving from SPICE to Direct Query was the issue.
SPICE has some supported data types that might not be the same you have in your database.
When you import data into SPICE, you get a status of the rows when they are imported and if there are any incompatibilities. In this case, it is possible that some of the data in your Database was not compatible with SPICE and that was causing the errors, therefore switching to Direct Query solved the issue.
I am glad you were able to sort this one out, if you plan to switch back to SPICE at some time, look at the status of the import to begin with the troubleshooting.
