topic Hierarchy with dimension fields in QlikView

Hierarchy with dimension fields

Tue, 25 Nov 2014 12:08:03 GMT

Hello,

i have some difficulties with the Hierarchy-Topic.

I need the Hierarchy but I also need the Different NodeName-Levels as Dimensions (which I need to rename).

AND additionally I want to have the tree-view of of Hierarchy-Path in a pivot-table.

How can I realise that? Please find attached the neccessary files.

Very much thanks for your help in advance.

Re: Hierarchy with dimension fields

tresB — Tue, 25 Nov 2014 12:37:05 GMT

Could labelling could be an option for renaming the dimensions? Else, you might have to rename them in the script itself.

Re: Hierarchy with dimension fields

Tue, 25 Nov 2014 12:43:35 GMT

I would love to do that. But where are these fields? They are not in the Script.

The HIERARCHY-Function is building this fields.

Could you please tell me where I can rename them?

Hierarchie:

HIERARCHY(NodeID, ParentID, NodeName, ParentName ,Node_Bezeichnung ,Hierarchie_PathName, '/' ,Depth )
LOAD NodeID,
ParentID,
NodeName,
NodeName as Node_Bezeichnung

FROM

(ooxml, embedded labels, table is [Hierarchy BIPO]);

Re: Hierarchy with dimension fields

hic — Tue, 25 Nov 2014 12:57:29 GMT

To rename the fields you can end the script with:

FieldNameMap:

Mapping Load FromFieldName, ToFieldName Inline

[FromFieldName, ToFieldName

NodeName1, RepLayout

NodeName2, SuperGroup

NodeName3, Group

NodeName4, SubGroup];

Rename Fields Using FieldNameMap;

To create a better Pivot table, I would do the following:

Create Pivot table
Add the 4 levels as Dimensions
Add Expression - Sum(Sales)? Or just '1' when you test.
Style - Indent Mode
Perhaps: Style - Use Only First Dimension Label
Presentation - Subtotals on top

HIC

Re: Re: Hierarchy with dimension fields

Tue, 25 Nov 2014 14:58:05 GMT

Hi Henrik,

the first step worked very well. Thanks.

Just one additional question:

Can I also load the remap from Excel.

I tried this but it did not work.

FieldNameMapping:

Mapping LOAD
[From_Field_Name,
To_Field_Name]

FROM
[..\files\assets_mapping.xlsx]
(ooxml, embedded labels, table is Hierarchie_Namen);
Rename Fields using FieldNameMapping;

Re: Re: Hierarchy with dimension fields

hic — Tue, 25 Nov 2014 15:03:16 GMT

It will work if you remove the incorrect brackets from the Load statement.

Is now: LOAD [From_Field_Name, To_Field_Name] FROM ...

Should be: LOAD From_Field_Name, To_Field_Name FROM ...

HIC

Re: Re: Hierarchy with dimension fields

Tue, 25 Nov 2014 15:22:16 GMT

Thank you very much.

It helped alot.

Re: Re: Re: Hierarchy with dimension fields

Wed, 26 Nov 2014 08:35:23 GMT

Hi Henrik,

one last question. How can I get the relation?

I have a field named postrulecode with the same entries and I wanted to match.

But in this case, Qlikview keeps the Name "NodeName4" and does not match.

Every other name I choose will be accepted but then I have no link.

Can you please help me again?

From_Field_Name	To_Field_Name
NodeName1	RepLayout
NodeName2	Hauptgruppe
NodeName3	Bipo
NodeName4	postrulecode

Re: Re: Re: Hierarchy with dimension fields

hic — Wed, 26 Nov 2014 09:12:58 GMT

The Rename statement can rename fields, but it cannot rename two different fields into one. The rename fails if there already exists a field with the target name. This is the reason why NodeName4 isn't renamed.

What you can do, is to load the Postrules table after the Rename statement. That will work.

HIC

Re: Re: Re: Hierarchy with dimension fields

Wed, 26 Nov 2014 10:03:56 GMT

Thank you very much.

It worked.