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

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

[jegadeesan]

Thanks Raj

[jegadeesan · Accepted solution]

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

Next

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

Next

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