So, here is the story. It was reported to me that there was missing data on a dataset related with 3 rows. Here’s my troubleshoot process:
- I first checked on a different platform (PopSQL) if the same query was retrieving these rows that were missing, and in fact they were without any issues, all the 3 rows and field data were there.
- I then created a brand new dataset on Quicksight, with the same query, but this time I’m filtering (on the where clause) by the ID of these 3 missing rows.
- When I did 2) in the Query Editor and clicked ‘Apply’, the following message appeared: “3 rows were skipped out of the 3 previewed”. So this message led me to this documentation: Troubleshooting skipped row errors - Amazon QuickSight
- I then published the dataset. I noticed that all the 3 rows were imported (when I click on the dataset to see its details and latest refreshes).
- Then I created an analysis with this dataset, and noticed that many of the data of these 3 rows were there, but other important data was missing (i.e null)! which Is the same that is happening on the original dataset.
- Now I go back to the Query Editor, and start commenting out chunks of fields to see if I am able to preview these 3 rows and I reached a point where I commented ONLY 1 field (let’s call it Y), and all the data that I want shows up!
The Y field is of decimal type, and for these 3 rows the Y field is NULL ( which I believe it should be OK because I also have plenty of other data where this field is NULL but still the remaining data shows up).
Any hints on how to solve this? I feel like I could do some workarounds such as make a ‘case when’ but wanted to understand what’s going on. Thank you