I have 2 tables in MATLAB- table A and table B, each having different dimensions (different no. of rows and columns). The first column of table A has the Date and time in format like 2018-11-01 12:00:00 (DateTime data format).
Now, in Table B, the Third and Fourth Column also consist of Date and Time in format like 2018-11-01 01:11:12:173000. What I would like to achieve is to remove all the rows (which are the data instances) from Table A, in case of which the Datetime for Table A falls in the range between the Date and Time in Table B. (To be more precise, suppose Table B has an entry of DateTime in third column for the first row/first data instance as 2018-11-10 12:30:00:173 and in fourth column as 2018-11-10 12:40:00:145, I would like to remove all data entries/rows from Table A, in case of which the DateTime Column value for Table A falls in the range of 2018-11-10 12:30:00:173 to 2018-11-10 12:40:00:145, as an example). This means that basically I would be removing the data in the aforesaid range from the Table A.
To approach this, the first thing which comes to my mind is to use inner join(), but, it is evident from Mathworks Community guidance that innerjoin() only matches the exact column value which I specify the Key as, but in this case, I would be looking at a range of DateTime values in 2 columns of table B, so perhaps this might not be the best approach. Using a for loop for this purpose might as well work, but would be quite complex and redundant with huge computational time on the large data in the tables. Any help in this regard would be highly appreciated.
I received some good answers to the question on the Mathworks Community page and the answers can be found at https://uk.mathworks.com/matlabcentral/answers/432509-how-to-remove-table-data-based-on-comparison-between-different-columns-in-another-table-in-matlab?s_tid=prof_contriblnk
I appreciate the answer by Guillaume (answered on the Mathworks Community page link mentioned above) and am putting it down here for future help to anyone:-
%inputs: TableA with a column named date, TableB with a column named datestart and dateend
%replace by actual table and variable names.
datetocheck = repmat(TableA.date, 1, height(TableB)); %replicate in as many columns as there are rows in B
datestart = repmat(TableB.datestart', height(TableA), 1); %tranpose and replicate in as many rows as in A
dateend = repmat(TableB.dateend', height(TableA), 1);
toremove = any(datetocheck >= datestart & datetocheck <= dateend, 2);
TableA(toremove, :) = [];