Hi,
I have a dataset that link 2 tables on uuid column (Postgres DB) with a full outer join.
I noticed most records a (~130k) link properly except for a few of them (~30).
A simple query in the database with the same join do bring those records so it isn’t a data issue.
I have no filter and the records disappear only after linking those 2 tables, otherwise Im able to see them in a dashboard (minus the linked data).
In order to debug I created a custom query that joins those tables directly and I added a condition to target 3 of the missing records + 1 non problematic record.
What is curious is that the dataset preview will show all the records, but a simple table visual in the dashboard would only display the problematic one + a row filled with null instead of the 3 missing ones.
Eg:
Does anyone know what could cause that or have any pointer?
Thank you!
1 Like
Hello @tomEver, welcome to the QuickSight community!
I am curious, when viewing the screenshot of the custom SQL, it looks like you might be writing the SQL statement as a dataset you are joining onto another. Is that correct? If you start from scratch on a new dataset, start with the custom SQL query, and paste in the code you shared above, I think it would work how you are expecting.
If you are joining 2 tables from the same data source, I always recommend utilizing SQL to create the joins rather than handling them with the built in QuickSight functionality. The process for updating the dataset will be a lot simpler on your end and it makes it easier to debug any errors that are occurring.
Let me know if that helps!
Hello @tomEver, I wanted to reach out since we have not received a response from you. Can you please provide more information related to the questions I asked in my previous message? With some more information about the issue you are facing, I can help guide you towards a solution. If we do not hear back from you in 3 days, I will archive this topic. Thank you!
Hi @DylanM,
sorry for the late reply.
It turns out the Joins were working properly.
Quicksight was ignoring records from first table I was trying because of corrupted data in one of the fields (for reference a timestamp containing invalid dates).
I was joining that table with the custom query with a left join, hence the nulls for the joined columns.
This is why the preview of the custom query was giving me the records I expected, but the final dataset didn’t have those records, because skipped buy the left join.
That being said I reached support and it is indeed recommended to join tables from the same schema with a custom query, so thanks for your insight.
Sorry for the misleading question. Wish I could change the title.
Cheers,
Thomas
1 Like
