How to load latest excel file using time stamp

[jegadeesan]

Hi,

How to load a latest excel file using time stamp ?

Regards,

Jegadeesan G

[miguelbraga]

Hey there,

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

Best regards,

D.A. MB

[nagarjuna_kotha]

can you please elaborate your question more?

-Nagarjun

[el_aprendiz111]

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]

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]

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

[nagarjuna_kotha]

Check below threads.

It might helps you.

Re: Re: Oldest Excel file LOADING

Re: Pick the latest excel file from folders

Re: How to get the last updated QVD?

Loading data based on modified file time

Load file based on timestamp

load the latest (timestamp) file

Load latest Excel file to Qlikview

-Nagarjun

[jegadeesan]

Thanks Nagarjuna....

[vikasmahajan]

Hi please find below link I have implemented the same

Incremental Load using Timestamps Multiple Excel

Vikas