How can I remove duplicate from a table after Left join in QuickSight?

Hi Team,
I need your help in urgent I am building an analysis by using two datasets with Left join but when I am trying to build the report it is showing me the duplicate record in second table.

PFB the two screen shots and their data one is with join and another is without join I just wanted the table as with out join.

There are a couple of ways to fix this.

First is to look into the left join and join on a more unique value if possible. However, I understand that might not be possible.

The second solution would be to make a calculated field that divides by the amount of duplicates.

I don’t know your data but you would need to find a way to figure out how many duplicates there are.

If you just have page Id and time spent before dropout then you would do this:

without_duplicates = count({time spent before dropout})/countOver({Page Id},[{Page Id}])

This will take you count of time spent before dropout and divided it by the amount of Page ID’s are counted for a particular Page Id (duplicates).

However, this isn’t going to work if you have other fields contributing to this count in the first place (i.e dates / other fields).

Let me know if this helps

Distinct_count({time spent before dropout}, [{Page Id}) should work.

1 Like

Hi, @Biswajit_1993. Did the response from @Max answer your question? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!