Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pra_kale
Creator III
Creator III

To Know Last Refreshed Date of Excel,CSV and Qvd's

Hi,


Before Reloading the already developed application I always required to check the last modified date of all the Qvd's, Excel files and csv used  in the application. The QVD's and excel files are not on the same path so I have to to go to each and every path and need to check the modified date. It is a painful task to check each and every file.


So is there any script which I can include in my already build application so by doing Debug with Limited load i can get in One Table All the QVD's,CSV, excel files modified date and their path..similar like in below given format.


   

NoPathModified Date
1E:\Qlikview\Sample.qvd09/01/2016 11.45
2E:\Qlikview\Test.csv12/12/2015 18.45
3E:\Qlikview\New folder\Name.xlsx02/01/2016 22.15

Thanks in Advance.

1 Solution

Accepted Solutions
Kushal_Chawda

For QVDS

Tab:

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'QVD' as FileExtension

From E:\Qlikview\*.qvd(qvd);

concatenate (Tab)

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'CSV' as FileExtension

From E:\Qlikview\*.CSV(txt);

concatenate (Tab)

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'xlsx' as FileExtension

From E:\Qlikview\*.xlsx;

View solution in original post

12 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Something like this will work:

Files:

Load * inline [

Path

C:\Users\Marcus Sommer\Desktop\abc.xlsx

C:\Users\Marcus Sommer\Desktop\xyz.xlsx

];

for i = 1 to noofrows('Files')

    FilesAndFileTime:

    Load peek('Path', $(i) - 1, 'Files') as Path, filetime(peek('Path', $(i) - 1, 'Files')) as FileTime Autogenerate 1;

next

- Marcus

Kushal_Chawda

For QVDS

Tab:

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'QVD' as FileExtension

From E:\Qlikview\*.qvd(qvd);

concatenate (Tab)

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'CSV' as FileExtension

From E:\Qlikview\*.CSV(txt);

concatenate (Tab)

First 1 LOAD filedir() as Path,

          filename() as FileName,

          filetime() as Modified_Date,

          'xlsx' as FileExtension

From E:\Qlikview\*.xlsx;

pra_kale
Creator III
Creator III
Author

Hi Kush,

Thanks...

The above script is working but it is providing me the details of all the files,QVD's store in the mentioned path.

But, I want the script should provide me the details only of the QVD's or csv's or excel's used in the script without mentioning any path..whether it is possible.

Means it might be possible few QVD's are from E:\ drive few from D:\..etc.

HirisH_V7
Master
Master

Hi,

May be like this,

for each File in filelist ('Your Path\*.*')

Folder:

Load '$(File)' as Name,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

LatestFile:

first 1

Load

Name,

FileTime,

1 as dummy

Resident Folder

Order By FileTime DESC;

drop table Folder;

NoConcatenate

[Data For Excel]:

LOAD *

FROM

$(File)

(biff, embedded labels, table is Sheet1$);

[Data For QVD]:

LOAD *

FROM

$(File)

(qvd);

There you can see in above script for Both Excel and QVD loading.

PFA,

Hope this Helps,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
HirisH_V7
Master
Master

Hi,

You can Change the Path for above post,

Another Procedure and change the path as this,

From D:\Qlikview\*.qvd(qvd);



Hope this helps,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
pra_kale
Creator III
Creator III
Author

Hi Hirish,

Thanks for your help!!

Your script is also running fine and providing me the latest updated file from that folder..

But, currently my requirement is little bit different on which Kush has provided the solution at one level, where I am getting the details of all the files available in the mentioned folders with their Last Refreshed Date, Type and Place, Below given is the out-put of Kush script..which is exactly I want.    

    

PathFileNameModified_DateFileExtension
E:\QlikviewAgency.csv02-01-2016 18:27:59CSV
E:\QlikviewActualTransactions.qvd09-08-2015 14:55:54QVD
E:\QlikviewAvg Term.xlsx10-10-2015 11:01:52xlsx

But one more level ahead, can I get the details only of the files,QVD's used in the script. For e.g. If in a script I have used only 3 Qvd's and 2 Xlsx files, then I am interested in the details of these files only and not of all the files. Because in a path may get 50 to 100 files.

So just want to check whether this is possible..

Thanks in Advance.

HirisH_V7
Master
Master

Hi ,

Check this,

For picking latest QVD-Data:


for each File in filelist ('Your Path\*.QVD')

Folder:

Load '$(File)' as Name,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

LatestFile:

first 1

Load

Name,

FileTime,

1 as dummy

Resident Folder

Order By FileTime DESC;

drop table Folder;

NoConcatenate

QVD:

LOAD *

FROM

$(File)

(qvd);

For picking latest Excel-Data:


for each File in filelist ('Your Path\*.xls')

Folder:

Load '$(File)' as Name,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

LatestFile:

first 1

Load

Name,

FileTime,

1 as dummy

Resident Folder

Order By FileTime DESC;

drop table Folder;

NoConcatenate

Excel:

LOAD *

FROM

$(File)

(biff, embedded labels,table is Sheet1$);

For picking latest CSV-Data:


for each File in filelist ('Your Path\*.csv')

Folder:

Load '$(File)' as Name,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

LatestFile:

first 1

Load

Name,

FileTime,

1 as dummy

Resident Folder

Order By FileTime DESC;

drop table Folded labels, delimiter is ',', msq);

NoConcatenate

Excel:

LOAD *

FROM

$(File)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);



You can  use all of them in different script pages .


Latest File picker.PNG


Hope this Helps,

Thanks,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
pra_kale
Creator III
Creator III
Author

Thanks Kush, as Always, for Your Help...!!!

pra_kale
Creator III
Creator III
Author

Thanks...Great..Hirish...!!!