SAP hierarchy - applymap question

[#1]

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

[Nicole-Smith · Accepted solution]

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;

[struniger]

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'

;

[#4]

Thank you!  The Hierarchy function did the trick.

[#5]

Hello Stefan,

How do you get the different levels in the Hierarchy? I get only the first level.

thanks for your help,

Franky

[struniger]

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