Forum Discussion
Count Occurrence with Index
Hello
I am working with a limited data set. I need to create a grouped index and my thought was to count each occurnace and fill down thereby giving me set or groups to work with. In my table below I would like the top Duty to count as 1 and the next Duty would be 2. I could then fill or group each set of Duty, Block and Rest with 1, 1, 1 and 2, 2, 2 etc. I cannot figure this one out. The data is limiting and I do not want to spend time manully
FlightAssignmentHoursPICSICOver-20 PIC
| 3000 | Duty | 15 | 1 | 2 | 0 |
| 3000 | Block | 13 | 1 | 2 | 0 |
| 3000 | Rest | 46 | 1 | 2 | 0 |
| 3000 | Duty | 18 | 1 | 2 | 0 |
| 3000 | Block | 12 | 1 | 2 | 0 |
| 3000 | Rest | 27 | 1 | 2 | 0 |
| 3000 | Duty | 14 | 1 | 2 | 0 |
| 3000 | Block | 12 | 1 | 2 | 0 |
| 3000 | Rest | 22 | 1 | 2 | 0 |
7 Replies
If you add an Index column (from 0) you can add another custom column like:
Number.IntegerDivide([Index], 3) + 1and that should do it.
I will try it now. Thanks
Hello Works great when we have a set number between each row. Now in this eaxmple beow there is no set number. The row sequence is the same "Show" then flights and "Debrief". There could be multiple flights. So, the set number is not always the same. The end Goal is to merge (join) the one set of days with the other. But your solution does with with a set number
DayFlightDeparture-Arrival
Show Time 15Jun Tu K4 807 CVG-BAH Debriefing Show Time 17Jun Th K4 807 BAH-HKG 18Jun Fr K4 860 HKG-NGO Debriefing Show Time 19Jun Sa K4 258 NGO-LAX Debriefing Show Time 20Jun Su K4 232 LAX-SYD Debriefing Show Time 24Jun Th K4 259 SYD-SIN Debriefing Show Time 25Jun Fr K4 259 SIN-HKG 26Jun Sa K4 258 HKG-NGO Debriefing You've switched the requirements on me. What's the desired output from that last sample data?
Hello the two original tables' data lacks a useful key to join the two tables tables. By using an idex based on the order of Duty, Block and Show Time I can then join the tables with an output I have below. Now I have some useful data. But without being able to increase the count for each Show Time it will not work, there could be two to three flights between the Show Times that need to be skiped.
Thanks
John Haase
PICSICOver-20 PICFlightDaysDayFlight2Hours3PIC4SIC5Fourth Pilot
3000 Duty 15 1 2 0 1 1 Show Time 1 2 3000 Block 13 1 2 0 1 1 Flight 13 1 2 1 3000 Duty 18 1 2 0 2 2 Show Time 1 2 3000 Block 12 1 2 0 2 2 K4 807 8 1 2 0 3000 Duty 14 1 2 0 3 3 Show Time 1 2 3000 Block 12 1 2 0 3 3 Flight 12 1 2 1 3000 Duty 6 1 2 0 4 4 Show Time 1 2 3000 Block 4 1 2 0 4 4 Flight 4 1 2 0 3000 Duty 16 1 2 0 5 5 Show Time 1 2 3000 Block 14 1 2 0 5 5 Flight 14 1 2 1 3000 Duty 22 1 2 1 6 6 Show Time 1 2 3000 Block 15 1 2 0 6 6 Flight 8 1 2 0 3000 Duty 17 1 2 0 7 7 Show Time 1 2 3000 Block 13 1 2 0 7 7 Flight 12 1 2 1 FlightDayFlightDaysAssignmentFlight2PICSICOver-20 PICHoursHours3
3000 1 1 Duty Show Time 1 2 0 15 3000 1 1 Block Flight 1 2 0 13 13 3000 2 2 Duty Show Time 1 2 0 18 3000 2 2 Block Flight 1 2 0 12 8 3000 3 3 Duty Show Time 1 2 0 14 3000 3 3 Block Flight 1 2 0 12 12 3000 4 4 Duty Show Time 1 2 0 6 3000 4 4 Block Flight 1 2 0 4 4 3000 5 5 Duty Show Time 1 2 0 16 3000 5 5 Block Flight 1 2 0 14 14 3000 6 6 Duty Show Time 1 2 1 22 3000 6 6 Block Flight 1 2 0 15 8 3000 7 7 Duty Show Time 1 2 0 17 3000 7 7 Block Flight 1 2 0 13 12
