topic Re: how to load lastest excel dynamically into qlikview in QlikView

how to load lastest excel dynamically into qlikview

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

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

Re: how to load lastest excel dynamically into qlikview

Gysbert_Wassenaar — Wed, 21 Dec 2016 12:51:23 GMT

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

;

Re: how to load lastest excel dynamically into qlikview

Chip_Matejowsky — Wed, 21 Dec 2016 12:53:29 GMT

Use QlikView Server/Publisher to schedule reloads of the QVW

Re: how to load lastest excel dynamically into qlikview

tripatirao — Wed, 21 Dec 2016 13:00:51 GMT

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

Re: how to load lastest excel dynamically into qlikview

tripatirao — Wed, 21 Dec 2016 13:30:46 GMT

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.

Re: how to load lastest excel dynamically into qlikview

Anonymous — Wed, 21 Dec 2016 14:07:40 GMT

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!

Re: how to load lastest excel dynamically into qlikview

Gysbert_Wassenaar — Wed, 21 Dec 2016 16:46:33 GMT

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;

Re: how to load lastest excel dynamically into qlikview

tripatirao — Thu, 22 Dec 2016 07:32:00 GMT

Dear Gysbert,

Getting attached error while implementing your code.

Regards

Tripati

Re: how to load lastest excel dynamically into qlikview

Gysbert_Wassenaar — Thu, 22 Dec 2016 07:40:58 GMT

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

Re: how to load lastest excel dynamically into qlikview

tripatirao — Thu, 22 Dec 2016 14:20:52 GMT

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

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