Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.