Extending a pre-existing hierarchy

Mon, 14 Mar 2011 10:02:05 GMT

I have a table loaded as follows:

LOAD NodeFullName,
node_name,
[Book Name] AS book_id,
[Node Leve1 1],
[Node Leve1 2],
[Node Leve1 3],
[Node Leve1 4],
[Node Leve1 5],
Depth,
Instrument
FROM
[Hierarchy.QVD]
(qvd);

I now want to extend this hierarchy by mapping the value for the field Instrument onto [Node Level X], where X=Depth+1.
NB1 This is only necessary when the book_id field is non-empty. NB2 we only have data for Node Levels 1-4, so no extra
Node Levels need to be created. However, the Depth of the Hierarchy will need to be updated as well, plus the NodeFullName,
and the node_name. Example:

Before:

NodeFullName = Holland~Amsterdam~Zuid,
node_name = Zuid,
[Book Name] AS book_id = Zuid,
[Node Leve1 1] = Holland,
[Node Leve1 2] = Amsterdam,
[Node Leve1 3] = Zuid,
[Node Leve1 4] = '',
[Node Leve1 5] = '',
Depth = 3,
Instrument = Rob Cohen

After:

NodeFullName = Holland~Amsterdam~Zuid~Rob Cohen,
node_name = Rob Cohen,
[Book Name] AS book_id = Zuid,
[Node Leve1 1] = Holland,
[Node Leve1 2] = Amsterdam,
[Node Leve1 3] = Zuid,
[Node Leve1 4] = Rob Cohen,
[Node Leve1 5] = '',
Depth = 4,
Instrument = Rob Cohen

Any suggestions are very welcome!

topic Extending a pre-existing hierarchy in QlikView

Extending a pre-existing hierarchy