Scripting - Intervalmatch

[#1]

Good morning,

Let´s assume there are two tables

a) HolidayHours, with fields EmployeeNr, Date, NrHours

b) EmployeeMaster, with fields EmployeeNr, DateFrom, DateTo, CostCenter

EmployeeMaster has records starting from say 2011, while HolidayHours has records starting say 2013. The script should deliver as a result one or several tables showing records from EmployeeMaster ONLY for dates in HolidayHours (for example, no EmployeeMaster records with DateTo before 2013).

I script

*******************

load * from HolidayHours;

load * from EmployeeMaster;

BridgeTable:

IntervalMatch ( Date, EmployeeNr)
Load distinct DateFrom, DateTo,, EmployeeNr Resident EmployeeMaster;
left join (HolidayHours)
load * resident BridgeTable
where len(trim(Date)>=0;

drop BridgeTable;

*******************

The IntervalMatch and LeftJoin work as I expect i.e. I get a table where I have a single matching record from EmployeeMaster for every HolidayHours record. However, I still have records from EmployeeMaster that do not match any HolidayHours record - how can I get rid of those?

Many thanks in advance,

Leonardo

[MK_QSL · Accepted solution]

Try

Inner Join (HolidayHours)

IntervalMatch(Date, EmployeeNr)

Load ValidFrom, ValidTo, EmployeeNr Resident EmployeeMaster;

Left Join (HolidayHours)

Load * Resident EmployeeMaster;

Drop Table EmployeeMaster;

[#3]

Thanks Manish, that delivers exactly what I was expecting. Just as the cherry on top, is there a way to achieve the same remaining records while keeping the two tables separate, as this is more intuitive from an end-user point of view?

[MK_QSL]

Its difficult to replace IntervalMatch.... I am having the same problem with my current application..