Hello,
We have our dataset on a refresh schedule and noticed we’re skipping rows for ‘UNSUPPORTED_DATE_FORMAT’; for example, ‘0001-01-01’ We need to be able to report on these records; these can be input errors from the customer, and the end user needs to see to know where their transactions have errors to resolve.
Is there any way to configure the settings to allow these records to pass through?
Hello @krushmere, what data source are you utilizing in QuickSight? You may need to utilize custom SQL to convert the date format into something that QuickSight will recognize. Converting to and ISO format may be the best route to ensure your date is ingested properly on refresh.
Here is a link to some documentation on supported date formats to provide more information:
Hi @krushmere - You have to identify samples and see why QS is skipping them.
Hi @DylanM - Is there a way, when QS is not able parse or import those rows, can be logged in a file or provide some samples for verification. The sample data will help in finding the root cause of the problem.
Regards - Sanjeeb
We know why they’re being skipped. We work with claim/EDI transactions, and the end user has the ability to input dates, sometimes these dates are wrong. For example, service date, hospital admit date, discharge date, etc. One of the rows that was skipped had a date ‘0205-01-01’ and the reason it was skipped in the ingestion report was ‘UNSUPPORTED_DATE_FORMAT’ (technically it’s in a correct format ‘YYYY-MM-DD’ even despite the fact that nobody was submitting EDI transactions back in the 3rd century, haha). We have reports that track claims with errors, and we need to be able to display these records so the end user is able to pinpoint where their claim had errors. Essentially we need a way to allow these records to pass through.
I agree with you @krushmere . If we want to allow it, possibly you can load the data as a string and then create another field ( calculated field) and change the format to date.
Hello @krushmere, I went ahead and marked @Sanjeeb2022 's last response as the solution. Please let us know if you need any further assistance on this topic. Thank you!
I don’t know if I would agree with that as being a solution–it would be nice to customize the SPICE ingestion settings. Does nothing like this exist currently? There’s no way for us to bypass what SPICE considers criteria met for rows to be skipped? Also, the date format is a correct format and it’s still skipping the rows, I haven’t heard a solution to that issue.
Hello @krushmere, you can’t really adjust the SPICE ingestion settings. The only thing I can think of would be to write a case statement to return a specific date that isn’t present in your data but also is within the last 100 years for the fields that are throwing the error. You can do something like return ‘1960-01-01’ instead and then that could also be the identifier to show which rows have the error.
Otherwise, the rows are going to continued to be skipped because they do not match the expected date format/output that QuickSight can manage.