Need help with association/selections in datamodel

[#1]

I have a problem in creating a datamodel in qlikview that works as desired (based on a different relational data model). I will give two descriptions of the problem one "simple" that may be enough if that can be solved, and one more extensive in case anybody would suggest an altogether different approach.

Simple version:

There are three tables with fact-entities, let's call them A B C which are connected through a connection table containing three columns, one for each primary key of A,B and C. A and C always have a connection to B, whereas only in some cases they may also have a connection to each other. The problem is the following: If I have the following connection table

B-key	A-key	C-key
2	2	-
2	-	2

(There may be several As and Cs per B so we can't have them all on one row)

I want the desired behaviour to be: When I select for example all "A"s from last week, I want to be able to count the number of "C"s connected to them (through B). But the problem is that when I select a number of As, qlikview only selects the first of the rows in the connection table above, and even though the correct B is selected that way, through the B-column in the connection table, not all the rows for that B are selected, and hence not the Cs connected to that B.

(If I select a B, then all As and Cs are selected as expected/desired)

Is there a way to make it work like this so that all Cs connected to a B, are selected when I select an A connected to that B?

Extended version:

The full story is more complex, but I think the rest is solved if the above problem can be solved. But if you have another suggestion or want the full story, here it goes: The initial data comes from a relational database which looks differently in two versions:

Version 1:

- All As have a foreign key to ONE B

- All Cs have a foreign key to ONE B

- There is an optional connection table also connecting several Cs to one A, but all need to be connected to the same B.

Version 2:

- All As have a foreign key to ONE B

- The C foreign key to B is removed

- The connection table is now 3-way and all Cs have a connection to the connection table, and there is at least one row in it for each C that connects it to one B (instead of the now removed direct foreign key)

- There may still be optional connections to As in the connection table

- Cs may now be connected to As belonging to different Bs than the Cs

We need to be able to count As, Bs and Cs, but also preferably if one selects any of them (A B or C), all the connected of the others should be selected (if I select a number of Cs, say from last week, the related As and Bs should be selected and counted etc).

As stated above, I think we have arrived at a model that handles the connections to a satisfactory degree, but the "simple version" of the problem above is the remaining problem - if I select an A or C in that version the corresponding As and Cs don't get selected but it stops at B (but it works as desired if I select a B, then all As and Cs are selected)

Any ideas?@

[rwunderlich]

How about

=count({<A=,B=P(B)>}C)

-Rob

[#3]

Hi,

ok thanks, I see what you mean, but what I tried to say was that I can't do that join because that would imply that there are "direct connections" between As and Cs, while we don't know of that's the case if I've only considered the A-B and C-B connections. There are also A-C connections (even without the B connection) that need to be handled and the information stored.

For example in your

B,A,C

1,1,3

1,1,5

means that A=1 is connected to C=3 and C=5, directly as well. But in the datamodel there can be direct connections, like these would be, but also only indirect connections, ie, they are only connected to the same C. I need to be able to distinguish between the two cases, and also storing that information in there somehow.

Perhaps I could add a fourth column in the total table that is a true/false indicating if that particular row also is a "real direct" connection between A and C...

Or maybe it's not possible to have it both ways, both knowing A-C connections, while using A-B-C connected data for most part.

[#4]

Rob,

that was cool, I hadn't seen the P() and E() operators before! I was thinking about documenting that as a manual user selection to achieve the count, so this may actually work for the counts.

The only reason I feel slightly reluctant to going this way is that A,B and C have some different dimensions, and if filtering on one of them, one would expect the dimensions for the others "in normal qlikview manner" to reduce their possible set accordingly to that selection, which is why is posed my question for a way to make the actual selection apply to all "B=2"s in my simple example above, which would mean the normal qlikview selections behaviour would follow.

Thanks for a good idea though, I guess it could be a last resort if we can't find a way to make the actual selections "hit" B and thereby spread to C as well (if an A was originally selected)

[#5]

Igor,

I can't understand why you are afraid about circularr refs in my solution?

28-05-2014 16:34 użytkownik "Igor Milososki" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Re: Need help with

association/selections in datamodel

reply from Igor Milososki<http://community.qlik.com/people/Milososki?et=watches.email.thread>in *App

Development* - View the full discussion<http://community.qlik.com/message/537284?et=watches.email.thread#537284>

[#6]

For anyone reading this later on, this is what we ended up doing:

While Robs answer could work as a workaround for the calculations themselves, since these 3 tables are at the center of the datamodel, too many dimension selections would not work the desired, "normal" qlikview-way if just having this (ie selecting a dimension on C would not give a selection of As, even though the counts in charts could be made correct).

So instead, we went with a variant of Dariusz suggestion. We join the data so that we rely on A-B-connections (turns out they are mandatory for the server software), and then join ALL Cs to ALL As on the same B. While this in itself looses the information about which Cs are also directly connected to As, we instead store this in another column, which says true/false for each connection.

So in the end, selecting a C, would select ALL As on the same B, and to filter out only direct selection, the user need to select "true" in the additional column to filter out only those directly connected A-Cs.