Hello,
I've got the following data source files:
File_01.02.2018.xlsx // plan for February
File_03.02.2018.xlsx // plan for February with changes
File_01.03.2018.xlsx // plan for March
... etc.
Question/ Issue:
Every time we get a new file with changes (within one month), we need to replace the data from a new file's date and till the end of the month.
P.S. The data doesn't contain any "date" fields etc. Date is stored only in the file's header.
Attempts:
I know that there's the REPLACE LOAD statement but it won't work for this case.
Data:
Replace LOAD
Store,
Week,
Day, // Thu, Fri,...
Flag, // 1 or null
Month(Date( Date#( right( FileBaseName(), 10) , 'DD.MM.YYYY'), 'DD-MM-YYYY')) as Month,
Year(Date( Date#( right( FileBaseName(), 10) , 'DD.MM.YYYY'), 'DD-MM-YYYY')) as Year
FROM
Expected result:
File_01.02.2018.xlsx
| Store | Thu | Fri | Sat | Sun |
|---|
| PN | 1 | | | |
| CHE | 1 | 1 | 1 | 1 |
| PN | 1 | | | |
File_03.02.2018.xlsx
| Store | Thu | Fri | Sat | Sun |
|---|
| PN | 1 | | 1 | 1 |
| CHE | | 1 | | 1 |
| PN | | | 1 | 1 |
01.02.2018 is Thursday and 03.02.2018 is Saturday so we need to start replacing from Saturday
Result plan for February:
| Store | Thu | Fri | Sat | Sun |
|---|
| PN | 1 | | 1 | 1 |
| CHE | 1 | 1 | | 1 |
| PN | 1 | | 1 | 1 |
With my best regards,
Evgeniy
