Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

silambarasan
Contributor

How to perform incremental Excel file loads where dates are written in Excel file names

How to do incremental load?  Extract date from file name for incremental

File Name:

SS_2016-01-31

SS_2016-02-01

NoteSmiley Very Happyay-1 file stored as qvd and then perform incremental load

gwassenaarhicswuehl

Message was edited by: Silambarasan M

Tags (2)
16 Replies
kkkumar82
Valued Contributor III

Re: How to perform incremental Excel file loads where dates are written in Excel file names

Can you elaborate your requirement ??

Re: How to perform incremental Excel file loads where dates are written in Excel file names

You could extract the dates from the filename like:

date(date#(subfield(filebasename(), '_', 2), 'YYYY-MM-DD'), 'MM/DD/YYYY')

- Marcus

Re: How to perform incremental Excel file loads where dates are written in Excel file names

silambarasan
Contributor

Re: How to perform incremental Excel file loads where dates are written in Excel file names

ya i tried but its not work

MVP
MVP

Re: How to perform incremental Excel file loads where dates are written in Excel file names

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.

vikasmahajan
Honored Contributor III

Re: How to perform incremental Excel file loads where dates are written in Excel file names

Do you want like this  Incremental Load using Timestamps Multiple Excel  ?

Vikas

Re: How to perform incremental Excel file loads where dates are written in Excel file names

Then there is something different by you - where did you try it:

- Marcus

silambarasan
Contributor

Re: How to perform incremental Excel file loads where dates are written in Excel file names

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) ;

silambarasan
Contributor

Re: How to perform incremental Excel file loads where dates are written in Excel file names

can't download your file..please attach

Community Browser