topic Re: Load Latest Excel file from folder in QlikView

Load Latest Excel file from folder

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have one folder like "Data" in this folder i am adding every day one Excel file .

20160101

20160102

20160103

20160104

20160105

i loaded the all files and i done the designing in front end .

Dimension :- NAME

Expression:SUM(SALES)

if i add one file tomorrow when i go for reload all files are loading and it was taken more time.

Here i want one reload the new file and i need to show all excel file data in front end?

How can i do this can any one file Example

Re: Load Latest Excel file from folder

qlikview979 — Mon, 26 Sep 2016 06:44:46 GMT

Sorry Check with this attachment

Re: Load Latest Excel file from folder

varshavig12 — Mon, 26 Sep 2016 06:53:34 GMT

Incremental load.

Re: Load Latest Excel file from folder

varshavig12 — Mon, 26 Sep 2016 07:02:24 GMT

Incremental load for beginner with example

Store in QVD with Incremenatal Load ( This is for reloading on monthly basis )

Re: Load Latest Excel file from folder

tresB — Mon, 26 Sep 2016 07:02:49 GMT

You could try with partial reload, something like:

LET vFilePath = 'D:\Excel\Data';

FOR EACH file in FileList('$(vFilePath)\*.xlsx');

LET vFileName = Left(file, 8);

LET vMaxPeriod = Rangemax(vFileName , vMaxPeriod);

NEXT

Data:

ADD LOAD *

FROM

[$(vFilePath )\$(vMaxPeriod).xlsx]

(ooxml, embedded labels, table is [Sheet1]);

Re: Load Latest Excel file from folder

jonathandienst — Mon, 26 Sep 2016 07:04:40 GMT

Something like this

// Search for latest file

Set zMaxFile = 0;

For Each zFile in FileList('Data\*.xlsx')

zFileBase = Num(SubField(SubField(zFile, '\', -1), '.', 1));

zMaxFile = RangeMax(zFileBase, zMaxFile);

Next

// And load latest file

Data:

LOAD *

FROM 'Data\$(zMaxFile).xlsx'

(ooxml, ...);

Adjust file paths and the load statement to your requirements;

Re: Load Latest Excel file from folder

qlikview979 — Mon, 26 Sep 2016 07:14:00 GMT

Hi Jonathan,

This code was given only latest file data

i want all files data

Regards,

Mahesh

Re: Load Latest Excel file from folder

qlikview979 — Mon, 26 Sep 2016 07:15:59 GMT

Hi Varsha,

If i go for Incremental load all files loading from starting right.

Re: Load Latest Excel file from folder

jonathandienst — Mon, 26 Sep 2016 07:22:17 GMT

Then amend it to be incremental:

// Search for latest file

Set zMaxFile = 0;

For Each zFile in FileList('Data\*.xlsx')

zFileBase = Num(SubField(SubField(zFile, '\', -1), '.', 1));

zMaxFile = RangeMax(zFileBase, zMaxFile);

Next

// And load latest file

Data:

LOAD *,

$(zMaxFile) as Src

FROM [Data\$(zMaxFile).xlsx]

(ooxml, ...)

// Load the remaining files (excluding latest file)

Concatenate(Data)

LOAD *

FROM [Data\History.qvd] (qvd)

Where Src <> $(zMaxFile);

// Store the history

STORE Data into [Data\History.qvd] (qvd);

Re: Load Latest Excel file from folder

qlikview979 — Mon, 26 Sep 2016 08:07:04 GMT

Hi Jonathan,

its not giving and how where condition will work.

Data:

LOAD *,

$(zMaxFile) as Src

FROM [Data\$(zMaxFile).xlsx]

(ooxml, ...)

// Load the remaining files (excluding latest file)

Concatenate(Data)

LOAD *

FROM [Data\History.qvd] (qvd)

Where Src <> $(zMaxFile);

Re: Load Latest Excel file from folder

qlikview979 — Mon, 26 Sep 2016 08:13:04 GMT

Hi Varsha,

Please Check my Second attached file. how data is file

Regards,

Mahesh

Re: Load Latest Excel file from folder

varshavig12 — Mon, 26 Sep 2016 08:26:17 GMT

Yes, it will load all the data.

But this time, it will load the data directly from the data source only for the new file i.e. 20160105

and the previous data from qvds.

Re: Load Latest Excel file from folder

jonathandienst — Mon, 26 Sep 2016 08:27:26 GMT

Are you saying it did not work, or you don't know how it works?

If it not working, you did include the first block of code -- thats where the variable is defined?