Clustering Hierarchies

[petergassert]

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

[MarcoWedel]

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

[petergassert]

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

[MarcoWedel]

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

[petergassert]

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