topic Re: Implementing a child-parent hierarchical datamodel using double relationships in QlikView

Implementing a child-parent hierarchical datamodel using double relationships

Tue, 27 Mar 2012 19:39:44 GMT

Let me first explain you the details about my context.

The child-parent hierarchical structure in my database model concerns PARTS (mechanical structures, electrical assemblies, bits and pieces, etc.) identified with part numbers (unique alphanumeric sequence) and each PART can (or not) be made of sub-components which also are PARTS (and therefore parts also have parents). However, the same PART can be used in more than 1 assembly (i.e. can have completely different parents)

(just to give you a better idea visually speaking)

I am aware that this might not be the usual hierarchical scenario, and this could make things harder, but I do not really need to have a hierarchical tree-view or anything like that (unless it's possible of course), basically I just want to be able to know which parts are the children and parents of 1 PART (even if it's just using basic highlighted SELECT fields).

Using MS Access, I made a model that looks like this (if you have a better idea please let me know) :

As you can see in the 2 tables shown here, it's BOMHierarchy that hold which parts are made with which other parts.
The problem here is that within 1 table there are 2 foreign keys referencing the same primary key.
I am afraid that such a "double relationship" would force me to duplicate (or "fork") the Part table into Part_Child and Part_Parent, causing all sorts of other tables to be duplicated in the same way (Part is pretty much the central point of my model).
If you don't understand what I am talking about (it's mostly about avoiding loops), please see my other thread here : http://community.qlik.com/message/204778

If you need I can provide you with a full screenshot of my whole Access data model.

Thanks a lot for your time

Re: Implementing a child-parent hierarchical datamodel using double relationships

pover — Tue, 27 Mar 2012 21:46:47 GMT

You should be able to use the Hierarchy() and HierarchyBelongsTo() functions without trouble. See the attached example.

Karl

Implementing a child-parent hierarchical datamodel using double relationships

Wed, 28 Mar 2012 15:11:15 GMT

Thanks for the document, it helped me understand how these functions work.
But it seems that it only works with Nodes that have 1 or 0 parent, and that the reference to the parent node is in the same table.

As you can see from the models in my original post, the nodes are stored in PART and the hierarchy (links between PART nodes) are held in BOMHIERARCHY.
I fail to see how I could use such Hierarchy() functions when there are 2 tables involved (Node table and Hierarchy table

Re: Implementing a child-parent hierarchical datamodel using double relationships

pover — Wed, 28 Mar 2012 17:39:42 GMT

Dominique,

QlikView hierarchy functions work with multiple parents. The example has a part called wheels that is found in skates and skateboards. QlikView can sometimes use the same model you have designed for operational systems, but it is usually better to create a dimensional model in QlikView or a Star Schema. In this case, you could have a parts catalog that is linked to a bridge table that is based on you BOMHierarchy table and helps link the parts catalog correctly to a fact table where you might have costs or quantity.

I've included a slightly more complex example in the post.

Implementing a child-parent hierarchical datamodel using double relationships

Fri, 30 Mar 2012 14:14:58 GMT

Hello Karl,

I have looked at your most recent hierarchy example and I have decided to duplicate my PART table into a PartCatalog, which would only contain the ID and the name, but the orignal PART keeps all the data and facts. The "bridge" table would be my BOMHierarchy table indeed.

However, since I only have the Personal Desktop edition, I have very limited uses to open your documents, so I would greatly appreciate if you could paste the load script here (or any other piece of code deemed useful).

I am struggling to fully comprehend the Hierarchy functions, but I am using the Reference manual to help me.

Thank you

Re: Implementing a child-parent hierarchical datamodel using double relationships

pover — Fri, 30 Mar 2012 14:47:20 GMT

Here's my script:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';
//Source Data
Part:
LOAD * INLINE [
    PartNumber_Part, Name_Part
    1000, SkateBoard
    2000, Skates
    1100, Board
    1200, Shoe
    1110, Wheels
];
BOMHierarchy:
LOAD * INLINE [
    Child_Part_BOMHierarchy, Parent_Part_BOMHierarchy
    1000, 1000
    2000, 2000
    1100, 1000
    1200, 2000
    1110, 1100
    1110, 1200
];
Fact:
LOAD * INLINE [
    PartNumber_Part, Cost
    1100, 5
    1200, 2
    1110, .1
];
//Data Model
Hierarchy_Bridge:
HierarchyBelongsTo(PartNumber_Part,Parent_Part_BOMHierarchy,PartNumber_Drop,PartNumber_Ancestor, Ancestor_Drop)
Load Child_Part_BOMHierarchy as PartNumber_Part,
          Child_Part_BOMHierarchy as PartNumber_Drop,
          Parent_Part_BOMHierarchy
Resident BOMHierarchy;
Part_Catalog:
Load PartNumber_Part as PartNumber_Ancestor,
           Name_Part
Resident Part;
Drop table BOMHierarchy, Part;
Drop fields PartNumber_Drop, Ancestor_Drop from Hierarchy_Bridge;