How do I pull data between specific dates like Vlookup formula in Excel?

I have flight data and ultimately what I want to find out is how many flights there are in which minute. Since I will be analyzing yearly data, my reference interval will start from 01/01/2019 00:00 and continue until 31/12/2022 23:59. What I am trying to do is to print the numbers in the "Total_Min_ATM" I created against the datetimes in the Reference_Table. For 2 days I have tried almost every formula for this but with no results.Also another problem is that when I use groupsummary in Seat_Capacity or Direct_Pax tables, it does counting, not summing. Thank you in advance for your help. I hope there is a solution! Best Regards,

[nmb,txt,allof] = xlsread('sampledata_uyyk.csv'); Maindata=allof; NumberData=nmb; [~, idx] = sort(datetime(Maindata(:,3),'InputFormat', 'MM/dd/uuuu hh:mm:ss aa'), 1, 'ascend'); SortedLastData = Maindata(idx,:); t1 = datetime ('01/01/2019 00:00:00','Format','MM/dd/uuuu hh:mm:ss aa'); t2 = datetime ('31/12/2022 23:59:00','Format','MM/dd/uuuu hh:mm:ss aa'); Reference_Table = transpose(t1:minutes(1):t2); Arr_Dep =SortedLastData(:,1); Scheduled_Time =SortedLastData(:,2); Actual_Time =SortedLastData(:,3); Flight_Number =SortedLastData(:,4); Dom_Int =SortedLastData(:,5); Airline_Code =SortedLastData(:,6); ATM_Number = SortedLastData(:,7); Fleet_Type = SortedLastData(:,8); Seat_Capacity = SortedLastData(:,9); Direct_Pax = SortedLastData(:,10); Transfer_Pax = SortedLastData(:,11); Total_Pax = SortedLastData(:,12); Destination = SortedLastData(:,13); Country = SortedLastData(:,14); Region = SortedLastData(:,15); NB_WB = SortedLastData(:,16); Real_Time = SortedLastData(:,17); Schedule_Ver = SortedLastData(:,18); ATM_Table = table(Actual_Time,ATM_Number); Total_Min_ATM = groupsummary(ATM_Table,"Actual_Time");

Answer 1

[Walter Roberson]

Create a timetable and index it with a timerange

https://www.mathworks.com/help/matlab/matlab_prog/subscript-into-times-of-timetable.html

Once you have the subset, you can retime with a function handle that returns the row count of what it has been passed

This approach might require some modification if what you have is a start time and minutes in the air rather than an entry for every minute. In such a case you would take the start times and add minutes () of the time in the air .... and you would need further steps after that

[Ugur Yilmaz]

I understand what you said, but isn't the "Reference_Table" variable already my timetable?

I just want to print the values in the "Total_Min_ATM" variable against the dates in the "Reference_Table" in a new variable. If there is no value, I want to write "0". Then I will add these values for every 60 minutes, for example, 13:21-14:20, 13:22-14:22.

[Walter Roberson]

Your code is difficult to read; you should format it so that we can understand it.

Reference_Table = transpose(t1:minutes(1):t2)

The transpose() operator is never valid on a table() or timetable() object, so Reference_Table is not a timetable() object. The : operator is also not valid on table() or timetable() objects, the part inside the transpose() is not a table or timetable object either.

timetable is a specialized type of table; see timetable

Answer 2

[Peter Perkins]

As Walter says, uses a timetable.

>> tt = readtimetable("sampledata_uyyk.csv")
[some warnings removed]
>> tt = convertvars(tt,["A_D" "FlightNo" "DOM_INT" "AirlineCode" "FleetType" "Destination" "Country" "Region" "FleetTypeGroup" "ScheduleVer"],"categorical");
>> tt.RealTime = datetime(tt.RealTime,Format="hh:mm a");
>> tt.RealTime = timeofday(tt.RealTime);
>> tt.RealTime.Format = "hh:mm";
>> tt
tt =
  1409×17 timetable
     ScheduledTime     A_D      ActualTime       FlightNo    DOM_INT    AirlineCode    Frq    FleetType    SeatCapacity    DirectPax_P2P_    TransferPax    TotalPax    Destination            Country             Region     FleetTypeGroup    RealTime    ScheduleVer
    ________________   ___   ________________    ________    _______    ___________    ___    _________    ____________    ______________    ___________    ________    ___________    ________________________    _______    ______________    ________    ___________
    09/03/0022 23:55    A    09/04/0022 00:03     3O0433       INT          3O          1       A320           180              170               0           170           FEZ        MOROCCO                     AFRICA      Narrow Body       00:03        IST0922  
    09/04/0022 00:50    D    09/04/0022 00:58     3O0434       INT          3O          1       A320           180              150               0           150           FEZ        MOROCCO                     AFRICA      Narrow Body       00:58        IST0922  
    09/04/0022 10:00    A    09/04/0022 09:22     3U3827       INT          3U          1       A333           305                0               0             0           CTU        China                       ASIA        Wide Body         09:22        IST0922  
           :            :           :               :           :            :          :         :             :                :                :            :             :                    :                   :             :              :             :     
    09/04/0022 13:00    D    09/04/0022 13:44     ZZ0018       INT          ZZ          1       CRJ2            15               10               0            10           FAB        ENGLAND                     EUROPE      Narrow Body       13:44        IST0922  
    09/04/0022 16:00    A    09/04/0022 15:21     ZZ0022       DOM          ZZ          1       FA8X            14                1               0             1           ADB        TURKEY                      TURKEY      Narrow Body       15:21        IST0922  
    09/04/0022 15:15    D    09/04/0022 16:20     ZZ0025       INT          ZZ          1       E135            13                8               0             8           HER        GREECE                      EUROPE      Narrow Body       16:20        IST0922  
	Display all 1409 rows.

I've chopped out some fairly self-explanatory warnings that can be dealt with using additional inputs to readtimetable, and the reformatting can also be handled as part of reading in data, using detectimportoptions (as can the warnings).

At this point, counting flights is just a call to retime.