Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Hierarchy with dimension fields

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.

Tags (2)
1 Solution

Accepted Solutions
Highlighted

Re: Hierarchy with dimension fields

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

Image1.png

View solution in original post

9 Replies
Highlighted
MVP
MVP

Re: Hierarchy with dimension fields

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

Highlighted
Not applicable

Re: Hierarchy with dimension fields

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

Highlighted

Re: Hierarchy with dimension fields

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

Image1.png

View solution in original post

Highlighted
Not applicable

Re: Re: Hierarchy with dimension fields

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;

Highlighted

Re: Re: Hierarchy with dimension fields

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

Highlighted
Not applicable

Re: Re: Hierarchy with dimension fields

Thank you very much.

It helped alot.

Highlighted
Not applicable

Re: Re: Re: Hierarchy with dimension fields

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?

hc_013.jpg

From_Field_NameTo_Field_Name
NodeName1RepLayout
NodeName2Hauptgruppe
NodeName3Bipo
NodeName4postrulecode
Highlighted

Re: Re: Re: Hierarchy with dimension fields

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

Highlighted
Not applicable

Re: Re: Re: Hierarchy with dimension fields

Thank you very much.

It worked.