One field is messing up the entire data

Question & Answer
, ,
1

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:

  1. 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.
  2. 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.
  3. 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
  4. 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).
  5. 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.
  6. 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! :face_with_raised_eyebrow:

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

2

Just so we are all on the same page, when you wrote “the Y field is NULL” are you saying that field had the four letters N, U, L, L in it, or are you telling us the field was empty, no space, nothing.

3

“The Y field is NULL” I mean that it is actually empty. No strings or blanks (" "), no decimals, just empty.

4

Any hints cgreenacre? :stuck_out_tongue:

5

Did you say you were using PopSQL? I’m not familiar with that flavor of SQL. Does it have either of these two functions coalesce() or nvl()? If so try replacing the field in the sql with this:
“coalesce(your_field, 0) as your_field” or “nvl(your_field, 0) as your_field”. This replaces the null value with a zero in your data before the QS dataset has a chance to process the data.

6

@cgreenacre PopSQL is basically another platform where we can make a connection and access a database. We use PostgresSQL on this platform. I tried with coalesce() but didn’t work. The Step 3 that I mentioned above still occurs when I use coalesce().

7

Hi @Serge

Just following up on this to see if you were able to work around or find the root cause of the error.

I’ve seen this before with strange characters being present in the data. Curious to know if this was the case here.

thanks!
Ramon

8

Hi @Serge
Since we have not received a response from you on this question, we are going to archive the question. If you still need this question answered, please create a new question topic. Thanks!