topic Re: SAP hierarchy - applymap question in QlikView

SAP hierarchy - applymap question

Wed, 22 Jan 2014 15:49:02 GMT

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

Re: SAP hierarchy - applymap question

Nicole-Smith — Wed, 22 Jan 2014 15:56:30 GMT

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;

Re: SAP hierarchy - applymap question

struniger — Wed, 22 Jan 2014 16:08:43 GMT

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'

;

Re: SAP hierarchy - applymap question

Mon, 27 Jan 2014 07:17:53 GMT

Thank you! The Hierarchy function did the trick.

Re: SAP hierarchy - applymap question

Tue, 18 Mar 2014 13:33:40 GMT

Hello Stefan,

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

thanks for your help,

Franky

Re: SAP hierarchy - applymap question

struniger — Mon, 31 Mar 2014 11:57:08 GMT

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