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

[tresesco]

Perhaps, you have to use Right Join rather than left join.

[MK_QSL]

Use Right Join or Outer Join rather than Left Join in your application..

[#4]

Thanks for your suggestion. As a result, I want to see records from EmployeeMaster only if they match records from HolidayHours, isn´t that what Left Join (HolidayHours) is supposed to do?

In any case, trying Right Join or Outer Join for a single EmployeeNr doesn´t change the outcome at least for the individual EmployeeNr I´m using as a test.

As a beginner, I am under the impression that the join statement is joining HolidayHours with BridgeTable not with EmployeeMaster. Since Intervalmatch is using ( Date, EmployeeNr) as key, guess it also uses a value where EmployeeNr is not blank but Date isn´t?

Any other suggestions?

[MK_QSL]

Can you provide both tables with some dummy data... say around 10-10 lines only in excel file?

[tresesco]

Intervalmatch() is supposed to take one argument. Have you checked if the generated tables are getting linked with a synthetic key or disconnected?

[#7]

An example is

EmployeeMaster

EmployeeNr	ValidTo	ValidFrom	CompanyCode	CostCenter
4711	28.06.2012	01.04.2011	150	1507009180
4711	31.12.2012	29.06.2012	150	1507009182
4711	31.12.9999	01.01.2013	150	1507009184
9876	31.01.2013	01.01.2013	101	1016009010
9876	31.12.9999	01.02.2013	101	1016009011
1234	30.09.2011	01.04.2011	104	1043493570
1234	31.12.2011	01.10.2011	104	1043493571
1234	31.12.2012	01.01.2012	104	1043493572
1234	31.05.2014	01.01.2013	104	1043493573
1234	31.12.9999	01.06.2014	104	1043493574

HolidayHours

EmployeeNr	Date	NrHours
1234	06.09.2013	96
1234	08.10.2013	96
1234	07.11.2013	48
1234	07.12.2013	48
1234	31.12.2013	16
1234	31.01.2014	240
1234	28.02.2014	240
1234	31.03.2014	240
1234	30.04.2014	240
1234	30.05.2014	224
1234	30.06.2014	216
1234	31.07.2014	184
9876	31.08.2013	128
9876	30.09.2013	128
9876	31.10.2013	120
9876	30.11.2013	120
9876	31.12.2013	96
9876	31.01.2014	320
9876	28.02.2014	320
9876	31.03.2014	320
9876	30.04.2014	208
9876	31.05.2014	192
9876	30.06.2014	192
9876	31.07.2014	192

And the script is

LOAD EmployeeNr,
     Date,
     NrHours

FROM
HolidayHours.xls
(txt, unicode, embedded labels, delimiter is '\t', msq);

Directory;
LOAD EmployeeNr,
     ValidTo,
     ValidFrom,
     CompanyCode,
     CostCenter

FROM
EmployeeMaster.XLS
(txt, unicode, embedded labels, delimiter is '\t', msq);

BridgeTable:
IntervalMatch ( Date, EmployeeNr)
Load distinct ValidFrom, ValidTo, EmployeeNr Resident EmployeeMaster;

Left Join (HolidayHours)
load * resident BridgeTable
WHERE len(trim(Date))>1;

drop table BridgeTable;

[#8]

Hi Tresesco,

the tables get linked with a synthetic key,

Best regards,

Leonardo

[tresesco]

I would still stick to my earlier opinion, the issue possibly with wrong number of arguments  in intervalmatch(); it should be one. Beyond that, I really could not understand your requirement. Could you post your expected output against the provided sample data?

[#10]

The result should be a table with all records from HolidayHours and only the matching records from EmployeeMaster. In below table, the records with a blank Date are not OK, the others are OK:

CostCenter	EmployeeNr	ValidFrom	ValidTo	Date	NrHours
1043493570	1234	01.04.2011	30.09.2011	-	-
1043493571	1234	01.10.2011	31.12.2011	-	-
1043493572	1234	01.01.2012	31.12.2012	-	-
1043493573	1234	01.01.2013	31.05.2014	06.09.2013	96
1043493573	1234	01.01.2013	31.05.2014	08.10.2013	96
1043493573	1234	01.01.2013	31.05.2014	07.11.2013	48
1043493573	1234	01.01.2013	31.05.2014	07.12.2013	48
1043493573	1234	01.01.2013	31.05.2014	31.12.2013	16
1043493573	1234	01.01.2013	31.05.2014	31.01.2014	240
1043493573	1234	01.01.2013	31.05.2014	28.02.2014	240
1043493573	1234	01.01.2013	31.05.2014	31.03.2014	240
1043493573	1234	01.01.2013	31.05.2014	30.04.2014	240
1043493573	1234	01.01.2013	31.05.2014	30.05.2014	224
1043493574	1234	01.06.2014	31.12.9999	30.06.2014	216
1043493574	1234	01.06.2014	31.12.9999	31.07.2014	184
-	-	-	-	-	-