topic Re: Need help with association/selections in datamodel in QlikView

Need help with association/selections in datamodel

Wed, 28 May 2014 11:49:55 GMT

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?@

Re: Need help with association/selections in datamodel

Wed, 28 May 2014 12:26:01 GMT

Igor,

do you have those datamodels in qvd? If it is possible please share, it will be easier to help.

regards

Darek

Re: Need help with association/selections in datamodel

Wed, 28 May 2014 12:46:57 GMT

Igor,

do you think, that if you will have table:

B,A,C

2,2,2

instead of above:

B,A,C

2,2,-

2,-,2

your first problem will be solved?

As you told, you have more than one A for one B, but i can imagine, that for exmaple you have:

B,A,C

2,1,-

2,2,-

2,-,2

But in that case if you will have:

B,A,C

2,1,2

2,2,2

your problem still should be solved.

To have this you may load first one part of this table:

B,A

then 2'nd:

B,C

In fact even having those 2 subtables loaded, your solution should work. But of course you may want to outerjoin those 2 subtables into one.

regards

Darek

Re: Need help with association/selections in datamodel

Wed, 28 May 2014 13:30:38 GMT

Thanks for the suggestions, but I don't think that would work for a couple of reasons:

1) I can't just put As and Cs arbitrarily on the same row, since there is information in that as well, ie, a C can belong to an A or not, or just be connected to the B, so that information need to be preserved somehow as well.

2) I don't see that it would work fully even we disregard the above, for example in your example above:

B,A,C

2,-,1

2,2,-

2,-,2

B,A,C

2,1,2

2,2,2

there is still the top row

2,-,1

which even if could put any other A in the empty space (which I can't due to (1) above), say I did

B,A,C

2,2,1

2,1,2

2,2,2

if I select C=1, it would still miss the middle row if counting A's.

So thanks, but I don't think it would quite do it.

Re: Re: Need help with association/selections in datamodel

Wed, 28 May 2014 13:38:39 GMT

I don't have any good "real" data I can give you but I have created a couple of qvds manually by only entering the actual ids in a db and creating the qvds, attached below. Requests is B in my case, reports is C and exams is A.

Currently the way I try do handle it is to strip any foreign keys from the actual data tables and instead create one connection table which contains all the connection info, I've attached the relevant part of the load script (not all script is here but I hope the relevant part).

But I think it may be difficult to get into that but perhaps easier to stay on the conceptual A B C level for discussion perhaps?

Re: Need help with association/selections in datamodel

Wed, 28 May 2014 13:39:41 GMT

Igor,

if you load this table as two subtables:

subtable1:

B,A

subtable2:

B,C

you will have all connections from C to A via B.

Try it, please.

Re: Re: Need help with association/selections in datamodel

Wed, 28 May 2014 13:47:17 GMT

Hi,

ah, ok that middle step would probably work for that part (I believe you about that, sorry)!

But if having them in 2 separate tables like that I would then have the problem of how to handle/know any "direct" A-C connections (without getting circular references). As it is now an example would be:

B,A,C

2,-,1

2,2,-

2,-,2

2,3,3

2,3,4

Where most of the data lacks connections between A and C, but connections can exist.

Sorry if that part wasn't clearly explained in the first part.

Re: Re: Need help with association/selections in datamodel

Wed, 28 May 2014 13:53:09 GMT

Igor,

are you from Poland?

Re: Re: Need help with association/selections in datamodel

Wed, 28 May 2014 13:56:27 GMT

Sorry no, Sweden if that helps, so it's just across the Baltic sea

Re: Re: Need help with association/selections in datamodel

Wed, 28 May 2014 14:04:23 GMT

polish names are often finished by "ski" I thought, that it may be easier to talk in polish language .

By the way, as I told, if two subtables works,

you may join them and you will have equivalent in one table.

For example:

subtable1:

B,A

1,1

1,2

2,1

subtable2:

B,C

1,3

1,5

2,1

2,3

joining those subtables you will get:

B,A,C

1,1,3

1,1,5

1,2,3

1,2,5

2,1,1

2,1,3

Application should give the same results as with those 2 subtables, but may work a little faster.

regards

Darek

Re: Need help with association/selections in datamodel

rwunderlich — Wed, 28 May 2014 14:24:33 GMT

How about

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

-Rob

Re: Re: Need help with association/selections in datamodel

Wed, 28 May 2014 14:27:37 GMT

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.

Re: Re: Need help with association/selections in datamodel

Wed, 28 May 2014 14:34:21 GMT

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)

Re: Need help with association/selections in datamodel

Wed, 28 May 2014 15:29:23 GMT

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>

Re: Need help with association/selections in datamodel

Mon, 09 Jun 2014 07:26:42 GMT

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.