Hierarchy with dimension fields

[#1]

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.

[tresesco]

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

[#3]

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]);

[hic · Accepted solution]

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:

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

HIC

[#5]

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;

[hic]

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

[#7]

Thank you very much.

It helped alot.

[#8]

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

[hic]

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

[#10]

Thank you very much.

It worked.