Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

petergassert
Not applicable

Clustering Hierarchies

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

Tags (2)
4 Replies
MarcoWedel
Not applicable

Re: Clustering Hierarchies

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

petergassert
Not applicable

Re: Clustering Hierarchies

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
Not applicable

Re: Clustering Hierarchies

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
Not applicable

Re: Clustering Hierarchies

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