how to take data of two tables only when the date and hour is the same?

Hi, I have two tables with the date in the first column and the hour in the second. The rest of the tables is other data. Now I want to merge the two tables, but only if the date and hour is the same. The two tables are attached

[VBBV]

Use the resource in below link

https://www.mathworks.com/help/matlab/ref/outerjoin.html

https://www.mathworks.com/help/matlab/ref/datetime.html

[Christopher Grahlmann]

Ok with inner/outerjoin it works. But now the final table is sorted with 01.01.2020 ->01.02.2020 -> 02.01.2020 -> 02.02.2020 (dd.MM.yyyy). So the dates are not in the right order anymore. Do you habe a tip how i can prevetn this?

Answer 1

[Seth Furman]

It is worth combining "Datum" and "Stunde" into a single variable, so they can be easily sorted.

For example,

>> t1 = readtable("table1.xlsx","TextType","string");
>> t2 = readtable("table2.xlsx","TextType","string");
>> t1 = head(t1);
>> t2 = head(t2);
>> % Convert text data to hours data
>> t1.Stunde = hours(str2double(extractAfter(t1.Stunde,2)));
>> t2.Stunde = hours(str2double(extractAfter(t2.Stunde,2)));
>> % Combine date and hours
>> t1.Datum = t1.Datum + t1.Stunde;
>> t2.Datum = t2.Datum + t2.Stunde;
>> % Remove redundant hours data
>> t1.Stunde = [];
>> t2.Stunde = [];
>> % Join tables
>> tJoin = innerjoin(t1,t2)

tJoin =

  8×6 table

           Datum            QBilanz    QBilanz_passiv    QBilanz_aktiv      Um      Qgesamt
    ____________________    _______    ______________    _____________    ______    _______

    17-Jan-2020 00:00:00       0             0                 0          408.64    -309.11
    17-Jan-2020 01:00:00       0             0                 0          411.61    -461.76
    17-Jan-2020 02:00:00       0             0                 0          413.09    -432.72
    17-Jan-2020 03:00:00       0             0                 0          411.54    -288.56
    17-Jan-2020 04:00:00       0             0                 0          412.39    -230.64
    17-Jan-2020 05:00:00       0             0                 0          409.04    -19.616
    17-Jan-2020 06:00:00       0             0                 0          407.92    -183.34
    17-Jan-2020 07:00:00       0             0                 0           408.7     24.757

>> % Sort tJoin if needed. Note that this is not actually necessary in this example.
>> sortrows(tJoin)

ans =

  8×6 table

           Datum            QBilanz    QBilanz_passiv    QBilanz_aktiv      Um      Qgesamt
    ____________________    _______    ______________    _____________    ______    _______

    17-Jan-2020 00:00:00       0             0                 0          408.64    -309.11
    17-Jan-2020 01:00:00       0             0                 0          411.61    -461.76
    17-Jan-2020 02:00:00       0             0                 0          413.09    -432.72
    17-Jan-2020 03:00:00       0             0                 0          411.54    -288.56
    17-Jan-2020 04:00:00       0             0                 0          412.39    -230.64
    17-Jan-2020 05:00:00       0             0                 0          409.04    -19.616
    17-Jan-2020 06:00:00       0             0                 0          407.92    -183.34
    17-Jan-2020 07:00:00       0             0                 0           408.7     24.757