Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to do incremental load? Extract date from file name for incremental
File Name:
SS_2016-01-31
SS_2016-02-01
Note:Day-1 file stored as qvd and then perform incremental load
Message was edited by: Silambarasan M
Can you elaborate your requirement ??
You could extract the dates from the filename like:
date(date#(subfield(filebasename(), '_', 2), 'YYYY-MM-DD'), 'MM/DD/YYYY')
- Marcus
ya i tried but its not work
Not 100% sure what you want to do.
You can get the date from filename using filename() and text functions like Textbetween() or subfield():
Re: Capturing a date that's embedded in an Excel filename
You can get all filenames in a folder and loop over this list using FOR EACH vFile IN FILELIST('Path') ... NEXT vFile
(have a look at the sample in the HELP file)
If you load your historic QVD first, get the last read date, then use the FOR EACH ... NEXT loop and filter all filenames with dates newer than the latest from QVD, you should get what I assume you want.
Do you want like this Incremental Load using Timestamps Multiple Excel ?
Vikas
Then there is something different by you - where did you try it:
- Marcus
Step 1:
Scenario:
LOAD ID,
Date(MakeDate(SubField(SubField(FileBaseName(),'_',4),'-',1),SubField(FileBaseName(),'-',2),SubField(FileBaseName(),'-',3))) as Date
FROM
[..\New\SS_*.xlsx]
(ooxml, embedded labels, header is 9 lines, table is [Container 1]);
STORE Scenario into [..\New\Scenario.qvd](qvd);
DROP Table Scenario;
Step 2:
Scenario:
LOAD ID,
Date
FROM
[..\New\Scenario.qvd]
(qvd);
Add:
Load Max(Date) as Maximum Resident Scenario;
Let vMaxDate=peek('Maximum ',0,Add);
Concatenate(Scenario)
LOAD ID,
Date(MakeDate(SubField(SubField(FileBaseName(),'_',4),'-',1),SubField(FileBaseName(),'-',2),SubField(FileBaseName(),'-',3))) as Date
FROM
[..\New\SS_*.xlsx]
(ooxml, embedded labels, header is 9 lines, table is [Container 1])
Where num(MakeDate(SubField(SubField(FileBaseName(),'_',4),'-',1),SubField(FileBaseName(),'-',2),SubField(FileBaseName(),'-',3))) > $(vMaxDate) ;
can't download your file..please attach