Intervalmatch to correct values

[takama13]

Hi all,

My database is a set of 2 Excel files.

Some fields are the same (and used to link the 2 tables), some fields are different.

In each table, I have a field with a date of events.

But some data (Service Code) are wrong and I would like to correct them based on an Excel file I created with range dates.

Here below are my tables :

Table1 :  

Service Code	Date	Event	Should be
MAN	01/06/2016	Event1	MAGE
MAN	01/05/2018	Event2	MTS
COL	01/06/2016	Event3	COLU
COL	01/06/2018	Event4	CJS
VES	01/01/2016	Event5	VESP
VES	01/07/2018	Event6	VESP
MTS	01/06/2016	Event7	MTS
MTS	01/06/2018	Event8	CTPS

Table2 :  

Service Code	Date	EventB	Should be
MAN	01/06/2016	Event1	MAGE
MAN	01/05/2018	Event2	MTS
COL	01/06/2016	Event3	COLU
COL	01/06/2018	Event4	CJS
VES	01/01/2016	Event5	VESP
VES	01/07/2018	Event6	VESP
MTS	01/06/2016	Event7	MTS
MTS	01/06/2018	Event8	CTPS
VIC	01/02/2017	Event9	VIC

Service Code correction:    

Service Code	From	To	New Service Code
MAN	01/01/2015	01/04/2017	MAGE
MAN	02/04/2017	05/05/2018	MTS
COL	01/01/2015	04/04/2017	COLU
COL	05/04/2017	01/01/2019	CJS
VES	01/01/2015	01/01/2019	VESP
MTS	26/04/2018	01/01/2019	CTPS

Here is the code I use to try and correct the tables :

Table1:

LOAD

     [Service Code],

     Date,

     Event,

     [Should be]

FROM

(ooxml, embedded labels, table is Feuil1);

Table2:

LOAD

     [Service Code],

     Date,

     EventB,

     [Should be] as [Should be 2]

FROM

(ooxml, embedded labels, table is Feuil1);

'Service Code correction':

LOAD

     [Service Code],

     From,

     To,

     [New Service Code]

FROM

(ooxml, embedded labels, table is Feuil1);

Inner Join ('Table1')

IntervalMatch ([Date],[Service Code])

LOAD [From], [To], [Service Code] Resident 'Service Code correction';

Right Join('Table1')

LOAD * Resident 'Service Code correction';

Inner Join ('Table2')

IntervalMatch ([Date],[Service Code])

LOAD [From], [To], [Service Code] Resident 'Service Code correction';

Left Join('Table2')

LOAD * Resident 'Service Code correction';

DROP Table 'Service Code correction';

DROP Field [From];

DROP Field [To];

The result is correct unless the fact that the lines with Service Code not present in 'Service Code correction' are removed.

I would like to keep them.

What am I doing wrong ?

Result for Table2:

Date	EventB	Service Code	Should be 2	New Service Code
01/01/2016	Event5	VES	VESP	VESP
01/06/2016	Event1	MAN	MAGE	MAGE
01/06/2016	Event3	COL	COLU	COLU
01/05/2018	Event2	MAN	MTS	MTS
01/06/2018	Event4	COL	CJS	CJS
01/06/2018	Event8	MTS	CTPS	CTPS
01/07/2018	Event6	VES	VESP	VESP

We can notice that the line with Service Code "VIC" disappeared. I would like to keep this line with 'New Service Code' column empty.

Thanks

[takama13]

Here are the files.

[Gysbert_Wassenaar]

Looks like a bug. No idea what's happening. As soon as I change the Date even a tiny bit like rounding it to a fraction of a second with round(Date,1/86400000) all is well.

[takama13]

I will use this workaround until someone could explain this bug.

Thanks for your time.