Modified Outerjoin function code

I have a Table(A) containing:

1. Company ID (ID)
2. Event A Date (DateA)
3. other irrelevant variables.

I have a Table(B) with:

1. Company ID (ID)
2. Event B Date (DateB)
3. other irrelevant variables.

A company can have several events (A or B).

I would like a code to add to Table(B) all the DateA that are 10 days before OR after Date B for a given company (red column in example).

There is the function outerjoin which I think can be used, but I'm not sure how to include the +/- 10 days factor.

Please find attach below an example.

Thank you for your help!

Example:

[Walter Roberson]

This refers to http://www.mathworks.com/matlabcentral/answers/275734-modified-version-of-vlookup-for-matlab

Answer 1

[Teja Muppirala]

This requires a bit more creativity, but this is how I would do it.

%% 1. Making the data (see my comment at the end)

A = table;
A.ID = [1;1;1;2;3];
A.DateA = datetime({'01-Jan-11'; 
                    '31-Jan-06'; 
                    '20-Jan-15'; 
                    '01-Jan-12'; 
                    '14-Jun-01'},'InputFormat','dd-MMM-yy','Local','en');
A.Irrelevant = [9;8;7;6;5];

B = table;
B.ID = [1;1;2;4];
B.DateB = datetime({'28-Dec-10'; 
                    '14-Jan-07'; 
                    '03-Jan-12'; 
                    '14-Mar-07'},'InputFormat','dd-MMM-yy','Local','en');
B.Irrelevant = [0; 1; 2; 3];

%% 2. Do the joining by checking day by day -10 to +10

B.order = (1:size(B,1))'; % Add this so we can unsort the dates back to the original order later

Acopy = A;
J = {};
for n = -10:10
    Acopy.DateB = A.DateA + n; %Shift the date by "n"
    J{end+1} = innerjoin(Acopy,B,...
                        'Keys',{'ID' 'DateB'},...
                        'RightVariables',{'ID' 'DateB' 'order'},...
                        'LeftVariables',{'DateA'});
end

J = cat(1,J{:}); % Accumulate the results
B_out = outerjoin(B,J,'mergekeys',true);

%% 3. Maybe not necessary, but we'll undo any sorting to put the dates in the original order

[~,ord] = sort(B_out.order);
B_out = B_out(ord,:);
B_out.order = [] % Remove the "order" column that we added

This gives out:

B_out = 

    ID      DateB       Irrelevant      DateA   
    __    __________    __________    __________

    1     2010/12/28    0             2011/01/01
    1     2007/01/14    1             NaT       
    2     2012/01/03    2             2012/01/01
    4     2007/03/14    3             NaT

By the way, instead of pasting an image of your data, it would generally be easier to help out if you included the raw numbers (or the code needed to generate them) as text so people can copy-paste.