counting only min values of one dimension based on related dimension
I have the following data model.
Quotes:
QTS_ID
AIR_TAIL_NUMBER
1
Alpha123
2
Lima566
3
Lima566
Quote legs:
QTL_ID
QTS_ID
QTL_PASSENGERS
D_DATE
D_AIRPORT
151
1
15
2020-06-03 12:00
Airport 1
152
1
15
2020-06-05 12:00
Airport 2
153
2
30
2020-06-06 12:00
Airport 3
154
2
30
2020-06-06 14:00
Airport 4
155
3
0
2020-06-12 12:00
Airport 4
Leg fees:
LGF_ID
QTL_ID
LGF_NAME
LGF_DELETED
5000
151
Fuel
-
5001
152
Aircraft fix
-
5002
152
Fuel
-
5003
153
Aircraft fix
-
5004
153
Fuel
-
5005
153
Navigation
Y
5006
154
Aircraft 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:
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.