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
