Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Load Latest Excel file from folder

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

12 Replies
Highlighted
Specialist
Specialist

Sorry Check  with this attachment

Highlighted
Specialist
Specialist

Incremental load.

Highlighted
Specialist
Specialist

Highlighted
MVP
MVP

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

Highlighted

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Specialist
Specialist

Hi Jonathan,

This code was given only latest file data

i want all files data

Regards,

Mahesh

Highlighted
Specialist
Specialist

Hi Varsha,

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

Highlighted

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Specialist
Specialist

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