Not getting data when we use left join

Question & Answer
, ,
1

I have a dataset with group_id and I want to join another dataset having groups_is as well
When I use left join, data from the right table is not showing up
Although if I run the same query on redshift I am getting the values

image
image790×481 17.2 KB

image
image708×565 23.6 KB

2

Hi @Vysh27

Can you share the details of your join in QuickSight dataset please?

3

image
image1338×673 38.3 KB

4

Hi,

As this is a left join, it is possible that the groups_id is empty if it does not exist on the trs_groups table. Can you check in your data preview or in a table in the analysis one group_id that does have a corresponding groups_id that does show correctly in your join in redshift?

Another option can be to use direct query with the join as you want it to be seen, but the join in QuickSight should work just fine.

Thanks.

1 Like
5

I have checked it and the groups_ id is present

image
image661×451 12.5 KB

6

To add to the point groups_id are present separately
but only when I join there’s no data coming

7

Looking at the data type and your example query the join should work without issues. This may have to be looked at closely in your environment. I suggest raise a ticket with AWS for this

1 Like
8

I encountered a similar situation before. For some reason, the joined data didn’t appear for certain rows during the data preparation stage. However, when visualizing the data in table or pivot table, all rows displayed the joined data correctly, with no missing values.

1 Like
9

Hi @Vysh27,

Do one thing try using other join options available in QuickSight and compare the results with querying the database. This will give us a clearer picture of how QuickSight handles joins.

Another thing: if you are getting one group_id as correct for the left join, why do you need the other group_id column values from different tables?

These are the things to check before reaching out to the AWS support team. First, we need to understand how QuickSight behaves when you join two tables.

Thanks & Regards,
Biswajit Dash

1 Like
10

Hi Vyshnavi @Vysh27 ,

QuickSight pulls sample data (1K) rows from your tables separately and uses that to paint the data preview within the dataset page. So, it is quite possible for you to see blank values in there as the rows pulled from table B might not contain the matches for the rows pulled from table A.
I understand that this can cause confusion (as it did in this case) and this feedback has been provided to dev team in the past.
For now, don’t worry about the missing values. If the join is setup right, go ahead and check the data from the analysis layer and that should give you the expected results.

Regards,
Arun Santhosh
Pr QuickSight SA

2 Likes