4 Replies Latest reply: Nov 14, 2016 4:46 AM by Peter Gassert RSS

    Clustering Hierarchies

    Peter Gassert

      Hi,

       

      I am trying to cluster hierarchie information but i have not found a solution.

      Deeper Qlik Sense knowledge is missing regarding scripting.

      May you can help me.

       

      I got a table with hierarchie information that is never ending in a table.


       

      IDParentIDFromTo
      1FRADKR
      21DKREZE
      32EZEMVD
      43MVDVCP
      54VCPDKR
      65DKRFRA
      76FRAKJA
      87KJAPEK
      98PEKKJA
      109KJAFRA
      1110FRAKJA
      1211KJAPEK
      1312PEKICN
      1413ICNKJA
      1514KJAFRA


      I want to build up rotations and want to cluster the information.

      When a rotation starts in 'FRA' until it ends in 'FRA', all rows between should be clustered within one Rotation number.

       

       

      IDParentIDFromToRotation Nr
      1FRADKR1
      21DKREZE1
      32EZEMVD1
      43MVDVCP1
      54VCPDKR1
      65DKRFRA1
      76FRAKJA2
      87KJAPEK2
      98PEKKJA2
      109KJAFRA2
      1110FRAKJA3
      1211KJAPEK3
      1312PEKICN3
      1413ICNKJA3
      1514KJAFRA3

       

      Do you have an idea, how to solve this best. The hierarchie function wasn't that successfull.

       

      Thanks for your help,

      Peter

        • Re: Clustering Hierarchies
          Marco Wedel

          Hallo Peter,

           

          maybe one solution could be:

           

          QlikCommunity_Thread_238750_Pic1.JPG

           

          tabFlights:
          LOAD RecNo() as FlightID, *
          Inline [
          AircraftID From To
          1 FRA DKR
          1 DKR EZE
          1 EZE MVD
          1 MVD VCP
          1 VCP DKR
          1 DKR FRA
          1 FRA KJA
          1 KJA PEK
          1 PEK KJA
          1 KJA FRA
          1 FRA KJA
          1 KJA PEK
          1 PEK ICN
          1 ICN KJA
          1 KJA FRA
          2 MUC BOS
          2 BOS MEX
          2 MEX RIO
          2 RIO DKR
          2 DKR MUC
          2 MUC GLA
          2 GLA KEF
          2 KEF HEL
          2 HEL MUC
          ] (delimiter is spaces);
          
          Join (tabFlights)
          LOAD FlightID,
              If(AircraftID<>Previous(AircraftID),From,Peek(RotationStart))  as RotationStart,
              If(AircraftID<>Previous(AircraftID),1,Peek(RotationNr)-(From=Peek(RotationStart))) as RotationNr
          Resident tabFlights;
          

           

           

          Grüße aus und nach Frankfurt

           

          Marco

          • Re: Clustering Hierarchies
            Peter Gassert

            Hi Marco,

            thanks for your reply. With peek it is an interessting possibility.

             

            Sadly this will not lead to the aim. My easy table example shows only a Rotation for one plaine. In real the Nodes, that describe one flight and the ParentNode is the only link to the flight before. With multiple plains all flight IDs are mixed up depending when the flight got stored in the respective system. Ordering the flight ID is possible, but sadly it does the still not help me to create the Clusters.

             

            Beside a Cluster identifyer i would like to  transform the Flights to a horizontal reading like "FRA-DKR-EZE-MVD-VCP-DKR-FRA"

             

            With the following function thie is working, but if the clustering i did not get it

             

            Hierarchie:

            Hierarchy(NodeID, ParentID,NodeName, ParentName, NodeName, PathName, '\', Depth)

            LOAD NodeID, ParentID,  From&'-'&To resident Temp order by NodeID asc;

             

            Beside this the function will not work, as the timeframe is getting to bigger.

             

            Do you see a possibility beside writing a script without Looping through the script?

             

            Thanks and Grrets from Frankfurt,

            Peter

              • Re: Clustering Hierarchies
                Marco Wedel

                Hi,

                 

                can you post an extended example including multiple planes?

                I guess your NodeID and ParentID fields initially are in a different table than the flight data (then combined in a "Temp" table)?

                Maybe a related sample of this flight table would help to understand your needs as well.

                I'm still not sure whether a hierarchy load is the best approach in this case because of the cyclic nature of your data.

                 

                regards

                 

                Marco

                  • Re: Clustering Hierarchies
                    Peter Gassert

                    Hi Marco,

                    sorry for the late reply.

                     

                    All relevant Data is in one table, so we have to track the Routing via the previous flight in the same table.

                    As result all Dep and Arr entries should be listed for every Rotation and Aircraft Registration.

                    The Rotation starts and ends in 'FRA' and should be finally clustered in a ID that references to the Rotation string like "FRA-ORD-MEX-GDL-DFW-FRA" for Registration 1.

                     

                    May you have an idea.

                     

                    Thanks and greets,

                    Peter

                     

                    Previous_Flight_ID (Parent NodeID)Flight_ID (NodeID)REGISTRATIONDEPARR
                    25010641672501031831Registration1JNBNBO
                    24033577092501031832Registration1NBOFRA
                    24123236012501047820Registration2FRAORD
                    25010478202501047821Registration2ORDMEX
                    25010478212501063246Registration2MEXGDL
                    25010632462501063247Registration2GDLDFW
                    24113660852501063248Registration2DFWFRA
                    24123512262501064167Registration1FRAJNB
                    25010318322501087276Registration1FRAIST
                    24094174332501087277Registration1ISTFRA
                    24122621352501100867Registration3FRAALA
                    25011008672501100868Registration3ALACAN
                    25011008682501103035Registration3CANALA
                    25011030352501103036Registration3ALAFRA
                    25010872762501112070Registration1FRAMAA
                    25011120702501112071Registration1MAAHYD
                    25011030362501119617Registration3FRABOM
                    25011196172501119618Registration3BOMHKG
                    25011120712501121829Registration1HYDSHJ
                    25011218292501121830Registration1SHJFRA
                    25010632482501144641Registration2FRABOM
                    25011446412501144642Registration2BOMHYD
                    24121119212501144644Registration2HYDSHJ
                    25011446442501144645Registration2SHJ

                    FRA