topic Re: How to load latest excel file using time stamp in QlikView

How to load latest excel file using time stamp

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

Hi,

How to load a latest excel file using time stamp ?

Regards,

Jegadeesan G

Re: How to load latest excel file using time stamp

miguelbraga — Fri, 30 Sep 2016 16:53:15 GMT

Hey there,

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

Best regards,

D.A. MB

Re: How to load latest excel file using time stamp

nagarjuna_kotha — Fri, 30 Sep 2016 16:59:58 GMT

can you please elaborate your question more?

-Nagarjun

Re: How to load latest excel file using time stamp

el_aprendiz111 — Fri, 30 Sep 2016 17:00:08 GMT

Example:

LOAD P*,

now() as Fec_Time

FROM

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

(ooxml, embedded labels, table is Process);

Re: How to load latest excel file using time stamp

MarcoWedel — Sat, 01 Oct 2016 02:14:48 GMT

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

Re: How to load latest excel file using time stamp

jegadeesan — Sat, 01 Oct 2016 03:23:39 GMT

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

Data:

LOAD *

FROM

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

Re: How to load latest excel file using time stamp

rajpreeths — Sat, 01 Oct 2016 03:41:07 GMT

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

Re: How to load latest excel file using time stamp

nagarjuna_kotha — Mon, 03 Oct 2016 04:20:46 GMT

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

Re: How to load latest excel file using time stamp

jegadeesan — Mon, 03 Oct 2016 04:39:30 GMT

Thanks Nagarjuna....

Re: How to load latest excel file using time stamp

vikasmahajan — Mon, 03 Oct 2016 04:42:14 GMT

Hi please find below link I have implemented the same

Incremental Load using Timestamps Multiple Excel

Vikas

Re: How to load latest excel file using time stamp

Anil_Babu_Samineni — Mon, 03 Oct 2016 05:23:48 GMT

How about this script, This script works file for me

Main:

Load * From Table;

INNER JOIN (Main) LOAD max(DateFile) as DateFile RESIDENT Main;