7 Replies Latest reply: Dec 24, 2009 1:03 AM by John Witherspoon RSS

    Data model association

    Tomer

      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

          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
              John Witherspoon

              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
                  Tomer

                  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
                      John Witherspoon

                      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
                          Tomer

                          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
                              John Witherspoon

                              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.