Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear QV experts!
I have a question concerning hierarchies in SAP (table KNVH customer hierarchy). For my report the table should be remoddeled that all the parent customers are on the same line.
Should I use the function Applymap?
Who can help me?
My original table:
Customer higherlevel
10 20
20 30
30 40
40 50
50 60
The purpose is to achieve this result:
Customer Parent1 Parent2 Parent3 Parent4 Parent5
10 20 30 40 50 60
20 30 40 50 60
30 40 50 60
40 50 60
50 60
Thank you
You should use the hierarchy function:
Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth])(loadstatement | selectstatement)
where
NodeID is the name of the field that contains the node id. This field must exist in the input table.
ParentID is the name of the field that contains the node id of the parent node. This field must exist in the input table.
NodeName is the name of the field that contains the name of the node. This field must exist in the input table.
ParentName is a string used to name the new ParentName field. If omitted, this field will not be created.
PathSource is the name of the field that contains the name of the node used to build the node path. Optional parameter. If omitted, NodeName will be used.
PathName is a string used to name the new Path field, which contains the path from the root to the node. Optional parameter. If omitted, this field will not be created.
PathDelimiter is a string used as delimiter in the new Path field. Optional parameter. If omitted, ‘/’ will be used.
Depth is a string used to name the new Depth field, which contains the depth of the node in the hierarchy. Optional parameter. If omitted, this field will not be created.
Example:
Hierarchy(NodeID, ParentID, NodeName) LOAD
NodeID,
ParentID,
NodeName,
Attribute
FROM data.xls (biff, embedded labels, table is [Sheet1$];
Here is another example:
OrgHierarchy:
Hierarchy (HierarchyOrgId, HierarchyOrgParentOrgId, HierarchyOrgName, , HierarchyOrgName, OrgTreeView)
LOAD OrgId as HierarchyOrgId,
ParentOrgId as HierarchyOrgParentOrgId,
Org as HierarchyOrgName,
OrgId
RESIDENT OrgTable;
You should use the hierarchy function:
Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth])(loadstatement | selectstatement)
where
NodeID is the name of the field that contains the node id. This field must exist in the input table.
ParentID is the name of the field that contains the node id of the parent node. This field must exist in the input table.
NodeName is the name of the field that contains the name of the node. This field must exist in the input table.
ParentName is a string used to name the new ParentName field. If omitted, this field will not be created.
PathSource is the name of the field that contains the name of the node used to build the node path. Optional parameter. If omitted, NodeName will be used.
PathName is a string used to name the new Path field, which contains the path from the root to the node. Optional parameter. If omitted, this field will not be created.
PathDelimiter is a string used as delimiter in the new Path field. Optional parameter. If omitted, ‘/’ will be used.
Depth is a string used to name the new Depth field, which contains the depth of the node in the hierarchy. Optional parameter. If omitted, this field will not be created.
Example:
Hierarchy(NodeID, ParentID, NodeName) LOAD
NodeID,
ParentID,
NodeName,
Attribute
FROM data.xls (biff, embedded labels, table is [Sheet1$];
Here is another example:
OrgHierarchy:
Hierarchy (HierarchyOrgId, HierarchyOrgParentOrgId, HierarchyOrgName, , HierarchyOrgName, OrgTreeView)
LOAD OrgId as HierarchyOrgId,
ParentOrgId as HierarchyOrgParentOrgId,
Org as HierarchyOrgName,
OrgId
RESIDENT OrgTable;
Hi Toby,
Here's what I'm using for the customer hierarchy
mCustomer:
MAPPING LOAD KUNNR,
(replace(ltrim(replace(KUNNR, '0', ' ')), ' ', 0) & ' - ' & NAME1) AS CustomerName
FROM
KNA1.QVD
(qvd)
WHERE LOEVM <> 'X'
;
Customer_Hierarchy:
Hierarchy (%Customer_ID, [Customer Group], [Customer Name])
LOAD
%Customer_ID
,[Customer Group]
,[Customer Name]
;
LOAD
KUNNR AS %Customer_ID
,HKUNNR AS [Customer Group]
,ApplyMap('mCustomer',KUNNR) AS [Customer Name]
FROM
KNVH.qvd
(qvd)
WHERE DATBI = '9999-12-31'
;
Thank you! The Hierarchy function did the trick.
Hello Stefan,
How do you get the different levels in the Hierarchy? I get only the first level.
thanks for your help,
Franky
Hi Franky,
Sorry for the slow reply - I was out on vacation and didn't do any Qlikview whatsoever (yes, this happens )
The different levels are generated by the HIERARCHY LOAD statement.
KNVH is linking Customers (KUNNR) with the parent (HKUNNR). The hierarchy statement resolves that and creates a table with the number of columns corresponding to the depth of the hierarchy tree.
Hope this helps.
Best regards
Stefan