Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Vilius
Contributor III
Contributor III

counting only min values of one dimension based on related dimension

I have the following data model.

Quotes:

QTS_IDAIR_TAIL_NUMBER
1Alpha123
2Lima566
3Lima566

 

Quote legs:

QTL_IDQTS_IDQTL_PASSENGERSD_DATED_AIRPORT 
1511152020-06-03 12:00Airport 1 
1521152020-06-05 12:00Airport 2 
1532302020-06-06 12:00Airport 3 
1542302020-06-06 14:00Airport 4 
155302020-06-12 12:00Airport 4 

 

Leg fees:

LGF_IDQTL_IDLGF_NAMELGF_DELETED
5000151Fuel-
5001152Aircraft fix-
5002152Fuel-
5003153Aircraft fix-
5004153Fuel-
5005153NavigationY
5006154Aircraft fix-

 

Basically there are Quotes which have Aircraft number assigned. Each Quote has flight Legs. The Leg has information about passengers, flight time, departure airport, etc. And then there are multiple Aircraft Fees for every Leg. These Fees can also be marked as Deleted in the database.

My first task was to get the most popular Departure airports (D_AIRPORT) and measure them by popularity, e.i. which one has the most Legs, but only with Legs which have "Aircraft fix" Fee assigned to it, and due to how Fees are calculated for some type of aircrafts, only Legs with passengers for these aircrafts.

So, I've created a chart-table with Dimension D_AIRPORT followed by complicated Measure using set analysis:

count({<LGF_NAME={"aircraft fix"}, LGF_ID=e({<LGF_DELETED={"Y"}>} LGF_ID)>
   *<AIR_TAIL_NUMBER={"*"}>
   *(
       <LGF_ID=e({<AIR_TAIL_NUMBER={"Lima566"}>} LGF_ID)>
       +<AIR_TAIL_NUMBER={"Lima566"},QTL_PASSENGERS={">0"}>
    )} [LGF_ID])

By adding aggr() I can even produce a map for these airports. It works well and fast.

Now I need to get the same data, but only counting first valid leg in a quote for all the airports. By "valid" I mean the one which follows the set analysis filter above. I suspect that I have to come up with rank() and min(D_DATE) here or something, but I cannot wrap my head around how to incorporate the filter above.

Thank you for your ideas in advance.

0 Replies