Showing one field but selecting another

[ma_pratt]

I'll use a hypothetical here that is similar to a case I'm working on. Let's say I have a database table that has name and  relatedKey within it. The relationship is a many to many, as a name might have null or many relatedKeys  and a related key will have 2 distinct names associated. I want to be able to create charts that when a name is selected, it actually filters not only that name, but all the names associated with it, along with any associated with them. Is this even possible? Thanks in advance everyone!

names	relatedKey
Abby	1		So if I selected Abby the following would be selected:
Adam	7		Abby
Allen	1		Allen
Betty	6		Adam
Bob	2		Bob
Carl	5		Dean
Cindy	3		Betty
Carrie	5		Carl
Dean	2		Carrie
Doug	6
Donna	4
Debbie	4
Earl	3
Abby	7
Adam	8
Allen	9
Betty	10
Bob	9
Carl	8
Adam	10

[Oleg_Troyansky · Accepted solution]

Hi,

Yes, this is possible, albeit tricky. I used an Alternate State that I named "A" and the following formula:

The filter on the right belongs to the alternate state and the filter in the middle belongs to the default state. The formula selects all values of the field "Name" that are associated with the values of the field "Code" that are in turn associated with selected Name in the default set.

Let me invite you to my lecture on Set Analysis and AGGR at the virtual Masters Summit for Qlik, that I will be teaching on March 1st. I will be going through many advanced Set Analysis examples, including something similar to this.

[marcus_sommer]

Depending on your real scenario you may not mandatory need a selection else an expression with a set analysis might be enough. Therefore you may try something:

count({< relatedKey = p(relatedKey)>} names)