0 Replies Latest reply: Dec 26, 2013 3:06 AM by Juan Gerardo Cabeza Luque RSS

    Hierarchies with Expressor

    Juan Gerardo Cabeza Luque

           Since I started working with Expressor I have been trying to replicate QlikView script functionality within Expressor dataflows, as I see Expressor projects are much easier to maintain, modify and work with in a multi-developer or multi-application environment.


           QlikView has very powerful functions and functionality to deal with hierarchies, like Drill-down groups, Hierarchy() and HierarchyBelongsTo() functions. This way QlikView is able to manage even they are balanced or unbalanced, fix-level or n-level, ragged or not ragged. There is a very good article from hic about hierarchies and how they are managed in QlikView here: http://community.qlik.com/docs/DOC-5334


           In my last projects with Expressor I have been dealing with hierarchies in dataflows, and I would like to share my experience with the QlikCommunity in case it could be useful for anybody.



      Although it is in the document attached, I am also writing here some lessons learned during this test:

      1. First, that it is possible generate hierarchies with Expressor the same way as with QlikView. Of course, it is easier with QlikView because all this logic is embedded in the Hierarchy() function. But it is much more powerful with Expressor as I have all the possibilities to add additional logic, filters and rules in the process.
      2. QlikView Hierarchy() function generates the level fields dynamically. In Expressor everything that acts as input or output in a dataflow must be defined (this gives you control over what happens in the dataflow and avoid unexpected changes). In this example I have created until a 7th level, but can be easily extended to any number of levels, and the fields not used are not filled so if you generate with this dataflow a 4 level hierarchy, the N5, N6 and N7 fields will be always NULL. So it could be said this dataflow is dynamic until the max level of design.
      3. Although this started as a concept exercise and it could be optimized, I am very happy with the result and I will use this dataflow the next time I have to build a hierarchy in an Expressor project, thus moving all this logic to Expressor and using the script only to load the final data model in QlikView.



      Attached you can find the following resources:

      • Hierarchies with Expressor.pdf: The document explaining my experience and with some details about the project.
      • WineHierarchy.zip: The Expressor project. You can inport this project directly with Expressor Desktop. Note I have been using Expressor 3.11 with QVD support. To use this project in your computer, you will have to edit the "Files" connection and write here the folder where the Winedistricts.txt file is in.
      • CompareHierarchies.qvw: The QlikView application used to compare both methods.
      • Winedistricts.txt: The source data, in a parent-child format.