topic Data model association in QlikView

Data model association

Wed, 23 Dec 2009 02:12:27 GMT

Hello,

I have an object model with multiple object types (mapped as joined-subclass).
For example sake, let's say that i have a base class - 'base' and 3 inheriting objects - A, B, C.
Each object has a unique ID (the id is a string, comprised of the <type>+<generated number>)
[a1, a2, a3... b1, b2, b3... c1, c2, c3...]

I can have refrences from one object to others, while the linked type is unknown at design time (A has a field that can refer to B or C). Each type can have multiple links.

How can I associate the objects in QV? Meaning, if ONE = b2 how will be b2 still be selected when I pick a1?

Will appreciate your help,
Tomer

Data model association

Wed, 23 Dec 2009 21:54:29 GMT

Hello,

Please refer to the Chapter 24.1 "Generic Databases" of the "QlikView Reference Manual" (p 479 for the V9 English version).

If you don't have it, please download it on www.qlik.com/download

it should helps you the tranform correctly your model into a QlikView compatible one.

Regards

Data model association

johnw — Thu, 24 Dec 2009 00:49:51 GMT

I'm not sure I understand the question, but maybe this sort of structure?

[Objects]:
LOAD * INLINE [
ID, Type, Field1, Field2
a1, a, 5, 10
a2, a, 15, 20
b1, b, 25,
b2, b, 30,
c1, c, , 35
c2 c, , 40
];
[Links]:
LOAD * INLINE [
ID, Link Type, Linked ID
a1, d, b1
a1, e, c1
a1, f, c2
b1, d, c2
b1, g, c2
];

In this case, objects of type a have both Field1 and Field2, objects of type b only have Field1, and objects of type c only have field2. We can then store any number of links retween these objects in a link table, with the link type describing what the relationship between the objects is. You might want to use a semantic load here instead of a regular load. Or if the relationships between objects are hierarchical, you might want to use a hierarchy load.

I'm not sure what you mean by "if ONE = b2 how will be b2 still be selected when I pick a1?" You can pick both b2 and a1 at the same time the same way you can pick two values in any list box, with a control click. But I'm guessing that isn't what you're asking.

Data model association

Thu, 24 Dec 2009 02:24:20 GMT

Thank you for the quick response.

I think I need to refine my problem (I'll refer to your example).

The correlation between the ID and type is right (a1 of type a, b4 of type b...).
The major issue here is that 'Field1' can hold values like b2, c5 etc'.

For example, for object a1, 'Field1' will hold a value of b1, and 'Field2' will hold a value of c3.
When selecting a1, b1 and c3 will automatically be associated (picked) as well.

Today I came across "Semantic Links". Could this be a good direction?

Tomer

Data model association

johnw — Thu, 24 Dec 2009 03:02:03 GMT

Field1 and Field2 in my model are SPECIFICALLY NOT links to other objects. If they WERE links to other objects, they would instead be implemented in the Links table. I was showing that you could associate information with an object directly, not just through links to other objects.

As I wrote it, selecting a1 will automatically "select" linked IDs b1 and c1, but only as linked IDs, which sounds like it isn't what you want. To be specific, if you select a1, and ask for sum(Field1), it will return 5, not 5+25 = 30. It sounds like you want all linked objects to be included, so you'd want the 5+25 = 30. But I'm still not clear exactly what you want here, and what your data is like. Do you just want that ID and directly-linked IDs to be included in totals? Do you want indirectly-linked IDs to be included? In other words, if a1 -> b1 -> d1 -> e1, do you want all four to be included in totals? Do the links form a hierarchy, or are they only a graph?

Regarding semantic links, that was what I was referring to when I said, "You might want to use a semantic load here instead of a regular load." I've never really been happy modeling graphs with semantic loads, but I'm sure they're perfect for some purposes.

Data model association

Thu, 24 Dec 2009 04:12:46 GMT

I don't need the entire chain (a1 -> b1 -> d1 -> e1), just the direct links (a1-> b1).
I can't include the linked object's fields in the same table, because the objects have many fields.

To be specific:

I have some item groups (more than a few). Each group has its own hierarchy.

On the other hand, I have an organization tree - each employee has a parent, and several "free" links.
I'm calling them free because those Fields can point to any of the above groups.

I need to make a graphs that will show for each selected employee the items he "owns", according to their level in the hierarchy (a graph is specific for a group).
Let's say items of type A have 4 levels, and the links are only to the lower level of items (4th).

The graph should start at a specific level (2nd or 3rd) which will show the aggregated amounts of the 4th level (all the linked items). Clicking on the graph will drill down to the lower level - from 3=>4, and the showing the specific items and their amounts.

Can this issue be addressed inside the chart and not by the data model?

If I can't explain the model good enough I can make a small example and post a screenshot...

Data model association

johnw — Thu, 24 Dec 2009 05:51:34 GMT

So is this the sort of information you want to store?

Jane Doe owns A131
John Doe owns A12, A132 and B27

Employee Hierarchy:
L1 L2
Jane Doe -> John Doe

Item Hierarchies:
L1 L2 L3 L4 Amount
A1 -> A12 -> A123 -> A1234 500
-> A1235 200
-> A124 -> A1241 100
-> A1242 300
-> A13 -> A131 -> A1311 700
-> A132 -> A1324 800
B2 -> B27 -> B271 -> B2719 400
-> B2710 500

Although item IDs in my example data tell you what their parent is, this wouldn't necessarily be true in your real data. It's just to help me keep things clear in the example, and we'd need to store the parentage in some other way. IDs are assumed to carry no meaning in your real data.

For the information above, you want a pivot table that initially looks like this?

Item Amount
Jane Doe A131 700
John Doe A12 1100
A132 800
B27 900

If you then click to expand A12, you want it to look like this?

Item Item Amount
Jane Doe A131 700
John Doe A12 A123 700
A124 400
A132 800
B27 900

So far so good, or am I still hopelessly lost? Even if that's good so far, I have to confess that I'm not sure how to accomplish it, and will have to do some experimenting. I expect that the solution will involve a hierarchy load for the hierarchies themselves. Not sure if the links from the employees to the items they own should be semantic links or just regular data links. And then I'm not sure how to pull it all together into a chart.

And I'm leaving on a two week vacation in about 15 minutes, so... well, hopefully someone else can help you if this is urgent. If nobody solves it in the next couple weeks, feel free to send me a private message reminding me to look at this.

Data model association

johnw — Thu, 24 Dec 2009 06:03:44 GMT

OK, I'm heading out. I'll attach what I have so far, which are items and employees in hierarchies.