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
QlikCommunity_Thread_238750.qvw 197.2 K
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
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,
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.
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,
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