7 Replies Latest reply: Oct 13, 2016 5:46 AM by Andre Toerien RSS

    Hierarchy question

    Andre Toerien

      Hi

       

      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

          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
              Andre Toerien

              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

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

                    • Re: Hierarchy question
                      Andre Toerien

                      Hi

                      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
                          Andre Toerien

                          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
                            Andre Toerien

                            Hi

                             

                            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;