topic Latest Excel File has to pick based on date and time stamp in QlikView

Latest Excel File has to pick based on date and time stamp

jegadeesan — Wed, 25 Nov 2020 16:16:04 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: Latest Excel File has to pick based on date and time stamp

jegadeesan — Tue, 04 Oct 2016 07:13:20 GMT

Thanks Raj

Re: Latest Excel File has to pick based on date and time stamp

jegadeesan — Tue, 04 Oct 2016 07:24:39 GMT

Hi All,

I have a scenario like latest excel file has to pick from the folder based on file name and has to pick two different file names latest has to pick respectively

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

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

I found a solution to above scenario. Here it is:

Let vFilePath ='E:\Data\VIM_Download_';

LET vFilePath1 = 'E:\Data';

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

LET vYears = mid(file,9,27);

LET vDates = mid(vYears,14,14);

LET vSM = Rangemax(vDates,vSM);

VAN2DATA:

LOAD *

FROM

[$(vFilePath)$(vSM).xlsx]

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

Store VAN2DATA Into $(vFilePath1)\VAN2DATA.qvd(qvd);

Drop Table VAN2DATA;

Let vFilePath2 ='E:\Data\KPI_ZDownload_';

LET vFilePath3 = 'E:\Data';

FOR EACH file1 in FileList('$(vFilePath2)*.xlsx');

LET vYears1 = mid(file1,9,28);

LET vDates1 = mid(vYears1,15,14);

LET vKPIData = Rangemax(vDates1,vKPIData);

KPIDATA:

LOAD *

FROM

[$(vFilePath2)$(vKPIData).xlsx]

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

Store KPIDATA Into $(vFilePath3)\KPIDATA.qvd(qvd);

Drop Table KPIDATA;

Thanks &Regards

Jegadeesan G