An appropriate use of synthetic keys?

[#1]

Greetings, I'm new to the community and new to Qlikview itself. We're on week two of working with it and I was hoping to get some input from the community in order to hopefully avoid any potential bad habbits.

Right now I'm trying to use a list box of certain people to select from a relationships table. Here are simplified versions of the tables:

Relationships:

• Person A ID
• Person B ID
• Relationship Type (Spouse, Child, Prospect Manager, etc.)

Constituents:

• Person ID

Basically what I want is a list box of people with a certain relationship, specifically the Prospect Manager relationship, and when I select a person from that listbox it selects only their Prospects (the people on the other side of the Prospect Manager/Prospect relationship). I've made the listbox with the list of Prospect Managers easily enough, what I'm hoping to know is what's the best way to select just their Prospects when I click on their name and not everybody for whome they have any relationship? For instance, if I click on Fred, I just want a list of Fred's prospects, not his prospects, kids, grandparents, friends, etc.

Now I've actually already done this by using a synthetic key. I made a table called Prospect Managers that grabs just the prospect managers and is linked to the Relationships table by Person A ID and Relationship Type. I use this table to populate the list box and the result is that when I click on one of their names I just get their prospects rows in the relationship table.

My real question is - is this a good idea? Is this a good use of synthetic keys or is there a better way I can do this? From reading some threads here I've learned that synthetic keys can have performance issues, so I'll keep my eye on that. I'm mostly wondering if there's an entirely different way that this is usually handled that would be better.

Lastly - appologies if this is a common topic that's been discussed before, I wasn't able to find a specific post about it though it could be that my searching was just poor. If this is the case feel free to just redirect me.

Thanks!

[#2]

I don't see why a synthetic key would be needed.

The link between the two table would be the relationship type and you would eitehr use an "if" statement in your dimesnion/expression to filter to "prospect" or a where clause if you were filtering in the load script.

Try this, only have the connection on relationship (no synth key) and have two list boxes, one for person and one for relationship, if you select Fred from the person listbox and "prospect" from the relation list box, does the object filter to what you want? if so, then your synth key is not needed.

you can use an expression/dimension like "=if(relationship='prospect', person)"

[#3]

Thanks for the reply. Yeah, the two list boxes work together, that's the exact functionality I want with a single list box.

However, I'm not sure I follow you beyond that. I do have a 'where' clause in my load script for the Prospect Managers table (the one I use to populate the list box) in order to just get the ID's of people with relationship type = prospect manager. But are you refering to using one somewhere else? I'm also not sure which expression/dimension I'd put the if statement in, since I don't see a spot for one on my list box.

I wonder if this is part of my confusion: I'm hoping for a selection in this list box to apply across all objects on all sheets of my application rather than just a specific object. Does that change things at all? What you're describing sounds exactly like what I'd like to be able to do with a list box or any other selection method - use if/then type logic to more specifically select things - I just haven't been able to find where to put it.

Also we're using 8.5 if that makes a difference.

Thanks again.

[#4]

I've never used anything preversion 9 so there may thing i refer too that may not be available.

The if/then doesn't need to be in the list box, you use it in whichever object you are displaying the related name in but ususing a where caluse in the laod script for the managers table is just as good. I think it may be as simple as aliasing or qualifing out the personid field in the managers table, something like this (not exact syntax)

maintable:

load

personid as maintable.personid

relation

from datasource

managers:

load

personid as managers.personid

relation

from datasource

where relation = 'prospect manager'

[#5]

Yeah, that's pretty much exactly what I'm doing for my managers table. It does work in the sense that it gives me a managers table that's just managers and when I select one of those managers in the list box it does filter the relationships table so that it only shows relationships with that person in them. However, it shows all of their relationships (spouse, child, etc.), not just the "Prospect Manager" relationships. It's only when I link the relationship type field between Relationships and Managers as well that it will grab only Prospect Manager/Prospect relationships when I select a manger.