recursive hierarchies (parent/child-hierarchies)

[#1]

Hello all,

we have just downloaded the personal edition two days ago and are trying to use it on our database. In the database most dimensions are stored as recursive hierarchy, i.e. every record has an ID (primary key) and a parent ID (which references another record in the table), e.g.

ID	PID	Text
1		Europe
2	1	Austria
3	1	Germany

Is it possible to use such hierarchies with QlickTech? If yes, how?

Searching the forums I have only found two relevant (quite old) threads [1] and [2] where all links point to non-existing webpages.

Thanks in advance and best regards,

Gerald

[1] iregular hirarchies problem

[2] hierarchy presentation

[#2]

Hello Gerald,

which version you are working with?

Since QV 8.5 there is a function called "hierachy" for the load-statement.

I think this could solve your problem.

Regards

Martin

[#3]

Hello Martin,

thank you for your response. In the meantime my workmates have already made some progress using HIERARCHY, although it took them quite a while for figuring out how to use it.

It looks like you cannot use an SQL SELECT statement directly with HIERARCHY. Instead you have to load your data and then use HIERARCHY in conjunction with RESIDENT. At least this is the only way they had success.

Now it is working for balanced hierarchies, but not for unbalanced hierarchies (which unfortunately is more common in our database). Is this a known limitation or do we have to set some special options?

Regards,

Gerald

[#4]

Hello Martin, hello Gerald,

I've tried out Gerald's advice:

X:
SQL SELECT
ID as IDTEST,
PARENT as PARENTTEST,
NAME as NAMETEST
FROM <oracle database name>;

Y:
Hierarchy ( IDTEST, PARENTTEST, NAMETEST ) LOAD
IDTEST,
PARENTTEST,
NAMETEST
RESIDENT X;

But i get an error message !? There is something wrong, i don't know why ? I think "RESIDENT" is the problem...

Have you an idea ?

Best regards,

HardyHeron

[#5]

I've found a solution. I have choosen another position for the sourcecode within the script (before some qualify and unqualify commands).

Now it works fine (:

[#6]

Hello,

I'm also trying to use the HIERARCHY load statement. I'm wondering what's the result of using this statement. Can you show the result?

Thanks!