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!
