topic Re: Joining With Date Tolerance in QlikView

Joining With Date Tolerance

mccook — Sat, 16 Nov 2024 18:01:37 GMT

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

Re: Joining With Date Tolerance

Kushal_Chawda — Thu, 01 Oct 2020 15:31:11 GMT

@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;

Re: Joining With Date Tolerance

marcus_sommer — Thu, 01 Oct 2020 15:29:49 GMT

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