Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jegadeesan
Creator
Creator

How to load latest excel file using time stamp

Hi,

How to load a latest excel file using time stamp ?

Regards,

Jegadeesan G

10 Replies
miguelbraga
Specialist III
Specialist III

Hey there,

Can you be more specific on your demand? Your question lack of explanation...

Best regards,

D.A. MB

nagarjuna_kotha
Partner
Partner

can you please elaborate your question more?

-Nagarjun

el_aprendiz111
Specialist
Specialist

Hi

Example:

LOAD P*,

     now() as Fec_Time

    

FROM

[..\Desktop\web_Qlik\BSE_WEB.xlsx]

(ooxml, embedded labels, table is Process);

MarcoWedel

Please elaborate on your requirements and take some time to close your open threads

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

jegadeesan
Creator
Creator
Author

Hi All,

I have a scenario like latest excel file has to pick from folder while loading based on file name.

Eg: filename = Man_hour_20150607053032.xlsx - (YYYYMMDDHHMMSS)  last 14 digit means date and time.

Am trying with below given script:

LET vFilePath = 'C:\Users\localuser\Desktop\Qlikview App\VIM Reporting\Data Files';

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

LET vYear = (Right(file,14);

LET vTimestamp = (Right(vYear,6);

LET vMission = left(vYear,4));

LET vMonth = mid(vYear,5,2);

LET vDay = mid(vYear,7,2);

LET vDateformat = Concat(vMission&vMonth&vDay&vTimestamp);

LET vDate = Timestamp(Timestamp#(vDateformat ,'YYYYMMDDHHMMSS'),'DD/MM/YYYY HH:MM:SS');

LET vFileYear =  Left(Right(file, 9), 4);                   // Gets the year portion from the filename

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

NEXT

Data:

LOAD *

FROM

[$(vFilePath\KPI_ZDownload_$(vMaxYear.xlsx))](ooxml, embedded labels, table is [Sheet1]);

rajpreeths
Creator
Creator

If the excel files are present in the single folder then make use of for each and peek function to identify latest date.

Sample code below.

For each fileextension '.xlsx'

Filetimes:

Filetime() as FileTime

Autogenerate 1;

Next fileextension

Latestdate:

Load

Max(Filetime) as MaxFileTime

Resident Filetimes;

Let vlatestDate=Peek('MaxFileTime',0,'Latestdate');

After this u can use variable vlatestDate to filter data from latest file only..

Hope this helps

jegadeesan
Creator
Creator
Author

Thanks Nagarjuna....

vikasmahajan

Hi please find below link I have implemented the same

Incremental Load using Timestamps Multiple Excel

Vikas