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

[#2]

Igor,

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

regards

Darek

[#3]

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

[#4]

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

to

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.

[#5]

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?

[#6]

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.

[#7]

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.

[#8]

Igor,

are you from Poland?

[#9]

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

[#10]

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