Replace the data from a new file's date

[Anonymous]

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

[bramkn]

Ah ok, I forgot about that.