Joining With Date Tolerance

[mccook]

Hi,

I'm looking for a little advice please, I have two tables like below:

Students

Name	ID	Expected Test Date	Notes
Lisa	122	01/10/2020
Luke	522	30/08/2020
Jack	321	15/09/2020
Luke	522	01/10/2020	Retest

Results

ID	Actual Test Date	Result
122	30/09/2020	Pass
522	31/08/2020	Fail
522	01/10/2020	Pass

What I'm looking to get to, by joining with a tolerance around the date, say 5 days either side of the expected test date:

Name	ID	Expected Test Date	Notes	Result	Actual Test Date
Lisa	122	01/10/2020		Pass	30/09/2020
Luke	522	30/08/2020		Fail	31/08/2020
Jack	321	15/09/2020
Luke	522	01/10/2020	Retest	Pass	01/10/2020

Any help, advice appreciated.

Cheers,

Dean

[Kushal_Chawda · Accepted solution]

@mccook  try below

Student:
LOAD Name, 
     ID, 
     [Expected Test Date], 
     Notes
FROM
[https://community.qlik.com/t5/QlikView-App-Development/Joining-With-Date-Tolerance/td-p/1748877]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Left Join
LOAD ID, 
     [Actual Test Date], 
     Result
FROM
[https://community.qlik.com/t5/QlikView-App-Development/Joining-With-Date-Tolerance/td-p/1748877]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);

Final:
LOAD *
where Flag_No_Actual_Date=1 or Flag_Matched_Expected_Date=1;
LOAD *,
     if(len(trim([Actual Test Date]))=0,1,0) as Flag_No_Actual_Date,
     if(Floor([Actual Test Date])>=floor([Expected Test Date]-5)  and Floor([Actual Test Date])<= Floor([Expected Test Date]+5),1,0) as Flag_Matched_Expected_Date
Resident Student;

DROP Table Student;

[marcus_sommer]

With a join approach it will be difficult but with an appropriate extended mapping (per while-loop) you may get your wanted results (as far as the real data are within the assumed data-range). You may try something like this:

M: mapping load
   ID & chr(1) & date([Actual Test Date] + pick(iterno(), -5,-4,-3,-2,-1,0,1,2,3,4,5)),
   [Actual Test Date] & chr(1) & Result
from Results while iterno() <= 11;

T: load *, subfield(applymap('m', ID & chr(1) & [Expected Test Date],
               'default' & chr(1)), chr(1), 1) as [Actual Test Date]
from Students;

- Marcus