6 Replies Latest reply on Jan 13, 2019 8:55 AM by Sander Van den Nieuwendijk

    Creating a hierarchy by self joining

    Sander Van den Nieuwendijk

      Hello,

       

      I'm struggling with hierarchies in Tableau. My input is a table with all objects, objectnames and parent objects, in total 5 levels. I want to use this hierarchy for collecting all kind of information per object which is not included in the twbx attached. Of course I want to be able to aggregate per hierarchy level. I understand I either need some SQL statement (which I cannot write) or use a number of self joins equal to the number of hierarchies.

       

      I created a really silly sheet and experimented. I think I get the point and calculated the level of the object in the hierarchy. But now I got stuck. How do I make a sheet where this hierarchy is being shown correctly with object on level one on the first column, corresponding objects in the second and so forth.

       

      (language of the object names is Dutch, in case you are wondering).

       

      Thank you very much for any assistence!

        • 1. Re: Creating a hierarchy by self joining
          Yuriy Fal

          Hi Sander,

           

          A usable hierarchy in Tableau would be a 'balanced' one:

           

          -- only 'leaf' rows are present in the datasource;

           

          -- every level has it's own column (of non-Null values);

           

          -- when a hierarchy is ragged, the (non-Null) values

          are propagated from the higher levels to the lower ones.

           

          Please find in the attached an example of such hierarchy.

          Hope it could help a bit.

           

          Yours,

          Yuri

          2 of 2 people found this helpful
          • 2. Re: Creating a hierarchy by self joining
            Sander Van den Nieuwendijk

            Hello Yuri,

             

            Many thanks for your help. To make sure I understand you correctly: in order to follow your 3-bullits solution you have created 5 calculated fields. These fields fill out NULL fields with the value of their direct parent level. Then you use the standard hierarchy function of Tableau to combine these 5 calculated fields into a hierarchy. If that is it: very simple and I like it. Really wondering why I read so many difficult solutions for this problem then...

             

            One thing remains: As soon as the object reaches it lowest level (but not the lowest hierarchy level of the entire dataset, the value now gets repeated on the subsequent levels. Is there a solution to that?

            • 3. Re: Creating a hierarchy by self joining
              Yuriy Fal

              Hi Sander,

               

              I like you like it :-)

              And you get the logic just right.

               

              The fine point is the join itself --

              it's the 'opposite' of your original one

              (the join columns are reverted all the way).

              So the hierarchy is built from the root down.

              That's why the Context (aka Datasource) Filter is applied.

               

              And for your remaining question, how would you like to see this?

              Which value should be written for the Planten at the Levels 4 and 5?

               

              Yours,

              Yuri

              1 of 1 people found this helpful
              • 4. Re: Creating a hierarchy by self joining
                Sander Van den Nieuwendijk

                In addition to my reply: I added some data which I want to see, mapped on the object tree we (well.. you) created. This starts to mess things up as you see in the attached workbook. In the workbook I entered some calculated fields which check with T/F whether the object name reached its deepest level, but I don't use these fields yet.

                 

                (Some use case background: I track the testresults whether requirements (linked to 1 or more object IDs) are tested succesfully. I store that in a system which can give me the results in tabular form.)

                • 5. Re: Creating a hierarchy by self joining
                  Yuriy Fal

                  Sander, could you please mockup the desired result (in Excel)?

                  I couldn't quite get the requirements (yet).

                  • 6. Re: Creating a hierarchy by self joining
                    Sander Van den Nieuwendijk

                    The result can look like this. Please note an object can have results connected directly to the object and have results from child objects.

                          

                    Own resultsOwn results + underlying objects
                    Object IDLevel 1Level 2Level 3Level 4Level 5OKNOKOKNOK
                    6Grond10133
                    1Huis41113
                    2Zolder0052
                    7Bed2052
                    8Dekens0101
                    9Kussens3131
                    31e verdieping1010
                    42e verdieping1010
                    5Tuin1010
                    10Planten0000
                    11Tuintafel0000
                    Sum / Check133TRUETRUE

                     

                    The excel file, including data and the desired output table is attached.