topic Re: Hierarchy question in QlikView

Hierarchy question

Wed, 05 Oct 2016 08:18:41 GMT

I have an issue with Hierarchies. I have setup 6 hierarchies in a model. I want to add any of the hierarchies to a pivot table, so if I do a selection on the hierarchy, I need it to be visible in the pivot chart. I have used the option with normal fields for a while to hide and show fields when selected as in the What's new in Qlikview 11

So I am using this GetSelectedCount(bud_dimension_flag)<>' ' AND GetSelectedCount([Budget Dimension])>0 in the chart Calculation Condition.

My question is how to get the hierarchy field in the list box to work the same way.

I have been looking a Groups as well and cannot decide which route toe to go.I am unsure how to get the hierarchy effectively loded as a drill down group.

Hope I am making sense. Please ask if I need to explain something better

Re: Hierarchy question

Gysbert_Wassenaar — Wed, 05 Oct 2016 08:21:48 GMT

I'm afraid I don't understand what you're trying to do. Can you post a small qlikview document that makes this clear?

Re: Hierarchy question

Wed, 05 Oct 2016 08:42:12 GMT

What I need to do with this is if I make a selection from one of the herarchies on XAxis I need it to be added as a dimension, for YAxis I need to have it appear as a column, I am not sure if I am am going to get it that far. I have set them up already with alternate states on each.

The hierarchies to 13 levels deep, so I am unsure should I go with hierarchies or groups.

Hope this is clearer

Re: Hierarchy question

Gysbert_Wassenaar — Wed, 05 Oct 2016 09:36:44 GMT

Not sure what you want. Perhaps the attached qvw helps.

Re: Hierarchy question

Wed, 05 Oct 2016 10:24:42 GMT

It is closer to what I want, but it should be adding Transfers and whatever is below it in the hierarchy if is dimension and sum the values in individual rows. I looks like hierarchies look nice in a list bob but when it comes to a pivot chart it does not really add value.

Should I rather go for drill down groups. I am just unsure how to add the data as a drilldown group

In the attached file it is how I get the data for one of the hierarchies. This hierarchy has 7 levels. Then I import the GUID column to the hierarchy so I can add the values from a table which I link via the GUID columns . Should I stay with the Hierarchies or is there a better way to import the data and then work with a drilldowngroup?

Any ideas of what I should do will be really helpful. I just need some pointers from more experienced people

Re: Hierarchy question

Wed, 05 Oct 2016 10:35:22 GMT

This is the sample what what it could look like. The users should choose what they want to wee as row data and what the want to see as columns

I am really hoping it makes sense

Re: Hierarchy question

Thu, 13 Oct 2016 08:58:30 GMT

Any ideas how to do this?

Re: Hierarchy question

Thu, 13 Oct 2016 09:45:35 GMT

I have identified part of my problem. In the load I am creating a hierarchy, then I am adding a field afterwards with which I can link the hierarchy to the data. The link though does not work, even though I do a concatenate load to the hierarchy it is loading extra rows, not doing a concatenate. Is this because it is a hierarchy table that it is not adding the extra column

Here are my two scenarios

Scenario1 - this option adds the extra rows but does not concatenate. This option loads the ProjectSegmentGUID on the extra rows created

ProjectHierarchyLoad:

SQL SELECT [SegmentID] as ProjectSegmentID

,[ParentSegmentID] as ProjectParentSegmentID

,[SegmentGUID] as ProjectSegmentGUID

,[FinancialYear] as ProjectFinancialYear

,[LevelType] as ProjectLevelType

,[SegmentCode] as ProjectSegmentCode

,[LevelDescription] as ProjectLevelDescription

FROM [V_F_Project];

ProjectHierarchyTest:

Hierarchy(HProjectSegmentID,HProjectParentSegmentID, HProjectLevelDescription, , HProjectLevelDescription ,ProjectLevelDescriptionName,'/',ProjectDepth)

Load

ProjectSegmentID as HProjectSegmentID,

ProjectParentSegmentID as HProjectParentSegmentID,

ProjectLevelDescription as HProjectLevelDescription

Resident ProjectHierarchyLoad ;

ProjectHierarchyTest:

Concatenate Load ProjectSegmentGUID, ProjectSegmentID as HProjectSegmentID

Resident ProjectHierarchyLoad;

Scenario 2 - This loads it into the same row, but it is not bringing the value over to the ProjectHierarchyTest table even though the ProjectSegmentGUID field on ProjectHierarchyLoad does have data

ProjectHierarchyLoad:

SQL SELECT [SegmentID] as ProjectSegmentID

,[ParentSegmentID] as ProjectParentSegmentID

,[SegmentGUID] as ProjectSegmentGUID

,[FinancialYear] as ProjectFinancialYear

,[LevelType] as ProjectLevelType

,[SegmentCode] as ProjectSegmentCode

,[LevelDescription] as ProjectLevelDescription

FROM [V_F_Project];

ProjectHierarchyTest:

Hierarchy(HProjectSegmentID,HProjectParentSegmentID, HProjectLevelDescription, , HProjectLevelDescription ,ProjectLevelDescriptionName,'/',ProjectDepth)

Load

ProjectSegmentID as HProjectSegmentID,

ProjectParentSegmentID as HProjectParentSegmentID,

ProjectLevelDescription as HProjectLevelDescription

Resident ProjectHierarchyLoad ;

ProjectHierarchyTest:

Concatenate Load ProjectSegmentGUID

Resident ProjectHierarchyLoad;