Ignore duplicates in a chart

[JenDM27]

Hello,

I'm no set analysis expert and I'm struggling to find a solution to this problem, so here I am!

I have a dataset with duplicate values. My dataset has a PersonId and a RelationshipType, each PersonId can have multiple, different RelationshipTypes. Let's say my table is as follows:

PersonID	RelationshipType
772	Partner
772	Child
771	Parent
773	Other
774	Child
775	Other
775	Child

I want a visualisation to return these results - i.e., only counting each person once and excluding duplicates, returning the first RelationshipType, sorted on PersonId:

RelationshipType	Count
Child	1
Partner	1
Parent	1
Other	2
TOTAL	5

I'm more than comfortable with Count (DISTINCT PersonId), which returns the unique count of PersonId, so the total displays the correct number when combined with RelationshipType, however, understandably, I currently get this:

RelationshipType	Count
Child	3
Partner	1
Parent	1
Other	2
TOTAL	5

Is there some set analysis statement to achieve this? I've tried so many combinations of functions and syntax but with no luck and I'm starting to feel like I'm banging my head up against a brick wall!

PS - my dataset is around 400,000 records per month and contains over 50 variables, but this is a simplified version! I also need the duplicates for every other visualisation in my app, so I can't remove them in the data load.

Thanks in advance!
Jen

[hanna_choi]

Dear @JenDM27 

How about this?

1. Data Modeling : We need to make flag about data duplicate.

2. Data Visualization  : Count({<Dup_Flag = {'1'}>}distinct PersonID)

[marcus_sommer]

Your data-structure seems to be a hierarchy. This kind of structure is quite difficult to evaluate with classical aggregations and conditional queries. Mostly it will be necessary to resolve the data-structure with specialized hierarchy approaches. A good starting point for the matter is: Hierarchies - Qlik Community - 1487801.