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

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

silambarasan — Thu, 12 May 2016 11:43:11 GMT

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

gwassenaar‌hic‌swuehl‌

Message was edited by: Silambarasan M

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

kkkumar82 — Thu, 12 May 2016 11:47:21 GMT

Can you elaborate your requirement ??

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

marcus_sommer — Thu, 12 May 2016 11:48:52 GMT

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

Kushal_Chawda — Thu, 12 May 2016 11:49:17 GMT

see this

Load only new or updated excel files dynamically

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

silambarasan — Thu, 12 May 2016 11:49:52 GMT

ya i tried but its not work

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

swuehl — Thu, 12 May 2016 11:51:10 GMT

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.

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

vikasmahajan — Thu, 12 May 2016 11:52:50 GMT

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

marcus_sommer — Thu, 12 May 2016 11:53:42 GMT

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

- Marcus

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

silambarasan — Thu, 12 May 2016 12:01:27 GMT

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

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

silambarasan — Thu, 12 May 2016 12:03:42 GMT

can't download your file..please attach

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

Anonymous — Thu, 12 May 2016 12:05:54 GMT

when the load the new excel and where date> max date

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

marcus_sommer — Thu, 12 May 2016 12:10:04 GMT

I'm not sure if your statement:

Date(MakeDate(SubField(SubField(FileBaseName(),'_',4),'-',1),SubField(FileBaseName(),'-',2),SubField(FileBaseName(),'-',3))) as Date

worked and it looked somehow different to your first example of the filenames but if they then use my suggestion.

Another point is the use of the variable which needs to be wrapped in single-quotes like:

Where ... > '$(vMaxDate)';

- Marcus

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

silambarasan — Thu, 12 May 2016 12:11:36 GMT

that is also i gave

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

Anonymous — Thu, 12 May 2016 12:15:31 GMT

you create the max date variable to create the max date in the script by using the peek function

example: let vmaxdate=peek(field name,-1, table name)

after that we can taken the new file to give the where date >vmaxdate

try in this way in this way you get the output

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

silambarasan — Thu, 12 May 2016 13:55:06 GMT

I edit the subfield value also but not works

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

silambarasan — Thu, 12 May 2016 13:56:51 GMT

I already create variable which you are saying..just verify my script please

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

marcus_sommer — Thu, 12 May 2016 14:01:41 GMT

I have seen another syntax-issue within the variable-creation, instead of:

Let vMaxDate=peek('Maximum ',0,Add);

it must be:

Let vMaxDate=peek('Maximum',0,'Add');

- Marcus