Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
ID | ParentID | From | To |
1 | FRA | DKR | |
2 | 1 | DKR | EZE |
3 | 2 | EZE | MVD |
4 | 3 | MVD | VCP |
5 | 4 | VCP | DKR |
6 | 5 | DKR | FRA |
7 | 6 | FRA | KJA |
8 | 7 | KJA | PEK |
9 | 8 | PEK | KJA |
10 | 9 | KJA | FRA |
11 | 10 | FRA | KJA |
12 | 11 | KJA | PEK |
13 | 12 | PEK | ICN |
14 | 13 | ICN | KJA |
15 | 14 | KJA | FRA |
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.
ID | ParentID | From | To | Rotation Nr |
1 | FRA | DKR | 1 | |
2 | 1 | DKR | EZE | 1 |
3 | 2 | EZE | MVD | 1 |
4 | 3 | MVD | VCP | 1 |
5 | 4 | VCP | DKR | 1 |
6 | 5 | DKR | FRA | 1 |
7 | 6 | FRA | KJA | 2 |
8 | 7 | KJA | PEK | 2 |
9 | 8 | PEK | KJA | 2 |
10 | 9 | KJA | FRA | 2 |
11 | 10 | FRA | KJA | 3 |
12 | 11 | KJA | PEK | 3 |
13 | 12 | PEK | ICN | 3 |
14 | 13 | ICN | KJA | 3 |
15 | 14 | KJA | FRA | 3 |
Do you have an idea, how to solve this best. The hierarchie function wasn't that successfull.
Thanks for your help,
Peter
Hallo Peter,
maybe one solution could be:
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
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
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
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) | REGISTRATION | DEP | ARR |
2501064167 | 2501031831 | Registration1 | JNB | NBO |
2403357709 | 2501031832 | Registration1 | NBO | FRA |
2412323601 | 2501047820 | Registration2 | FRA | ORD |
2501047820 | 2501047821 | Registration2 | ORD | MEX |
2501047821 | 2501063246 | Registration2 | MEX | GDL |
2501063246 | 2501063247 | Registration2 | GDL | DFW |
2411366085 | 2501063248 | Registration2 | DFW | FRA |
2412351226 | 2501064167 | Registration1 | FRA | JNB |
2501031832 | 2501087276 | Registration1 | FRA | IST |
2409417433 | 2501087277 | Registration1 | IST | FRA |
2412262135 | 2501100867 | Registration3 | FRA | ALA |
2501100867 | 2501100868 | Registration3 | ALA | CAN |
2501100868 | 2501103035 | Registration3 | CAN | ALA |
2501103035 | 2501103036 | Registration3 | ALA | FRA |
2501087276 | 2501112070 | Registration1 | FRA | MAA |
2501112070 | 2501112071 | Registration1 | MAA | HYD |
2501103036 | 2501119617 | Registration3 | FRA | BOM |
2501119617 | 2501119618 | Registration3 | BOM | HKG |
2501112071 | 2501121829 | Registration1 | HYD | SHJ |
2501121829 | 2501121830 | Registration1 | SHJ | FRA |
2501063248 | 2501144641 | Registration2 | FRA | BOM |
2501144641 | 2501144642 | Registration2 | BOM | HYD |
2412111921 | 2501144644 | Registration2 | HYD | SHJ |
2501144644 | 2501144645 | Registration2 | SHJ | FRA |