how to load lastest excel dynamically into qlikview

[tripatirao]

Dear All,

I want to load the lastest excel file into qlikview.

My excel files are in below format.

I have attached the qlikview file also which contain   code but I am not getting the output.

My requirement is load MIS November 2016.xlsx data into qlikview.

Please help me.

Regards

Tripati

[Gysbert_Wassenaar]

How do we know MIS November 2016.xlsx is the last? We can't use month names because Septmber is not a month which indicates that the file names may contain spelling errors.

If you want to load the file with the newest file date you can try something like this:

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

NEXT file

Data:

LOAD

     *

FROM

     [$(file)] (ooxml, embedded labels, table is [Sheet1])

     ;

[Chip_Matejowsky]

Use QlikView Server/Publisher to schedule reloads of the QVW

[tripatirao]

Hi Gysbert,

Thanks for your reply.

This is December Month,so we got November month data from business and In January 2017 we will get data from December 2016.

Please ignore the typing mistake ,it is actually September only.

Regards

Tripati

[tripatirao]

Hi Gysbert

I tried your code  but i am getting only MIS August 2016 data.

But i am not getting MIS November 2016 data.

Please do the needful.

[Anonymous]

Hi,

See attached setup to figure out the latest files.

May need a bit of tweeking when 2017 files arrive.

The files I used had dummy data but same file name formats...

Hope it helps!

[Gysbert_Wassenaar]

Perhaps something like this then

LET vFilePath = 'E:\Qllikview\ExcelTask';

SET vMaxYear = 0;

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

  LET vFileYear = Date#(SubField('$(file)',' ',2) &' '& SubField('$(file)',' ',3),'MMMM YYYY');

  IF $(vFileYear) > $(vMaxYear) THEN

       LET vLastFile = '$(file)';

  ENDIF

  LET vMaxYear = $(vFileYear);

NEXT

Data:
LOAD * FROM  [$(vFilePath)\$(vLastFile)] (ooxml, embedded labels, table is [Sheet1]);

EXIT SCRIPT;

[tripatirao]

Dear Gysbert,

Getting attached error while implementing your code.

Regards

Tripati

[Gysbert_Wassenaar]

Ok, try adding this line before the for-next loop: SET vMaxYear = 0; 

[tripatirao]

Dear Gysbert,

Thanks for your support

Following code is worked for me

LET vFilePath = 'E:\Qllikview\Incadea BI\Source'; 

FOR EACH file in FileList('$(vFilePath)\*.xlsx');   // Loops each excel file in the given Folder 

LET vFileYear =Date(Date#(Replace(SubField(SubField(file,'\',5),'.',1),'MIS ',''),'MMM YYYY'),'MMM YYYY');                    // Gets the year portion from the filename 

LET vMaxYear = Rangemax(vFileYear, vMaxYear);  // Gets the max year for every iteration 

NEXT 

LET vMaxYear=Date($(vMaxYear),'MMMM YYYY');

Data: 

LOAD *

FROM 

[$(vFilePath)\MIS $(vMaxYear).xlsx]      // Load Max year data 

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

Regards

Tripati