topic Re: inserting the timestamp of data source in qv application in QlikView

inserting the timestamp of data source in qv application

marksmunich — Mon, 01 Jul 2013 11:38:41 GMT

hello qlikviewers,

i would like to insert a timestamp which must display how old the source is or the status of the source file when it is update. for example i am using a excel data and i would like to display the date and time of the source file. or how old it is.

result must be like 26.04.2013 09:10:01

thanks

Mark

Re: inserting the timestamp of data source in qv application

marksmunich — Mon, 01 Jul 2013 12:19:03 GMT

this displays the last reload of the application. but i want to get the timestamp of the excel file. which is my source data.

Re: inserting the timestamp of data source in qv application

Mon, 01 Jul 2013 12:22:08 GMT

So you wanna display when the excel file was created?

Re: inserting the timestamp of data source in qv application

Mon, 01 Jul 2013 12:25:29 GMT

SET vTIMEStart = Now(); //When extraction Start

SET vTIMEEnd = Now(); //When extraction End

Inline:
     LOAD * Inline [
           StartTime,EndTime
           $(vTIMEStart),$(vTIMEEnd)];

Re: inserting the timestamp of data source in qv application

marksmunich — Mon, 01 Jul 2013 13:06:47 GMT

i want to display when the excel was last updated (it is same as when it is created)

Re: inserting the timestamp of data source in qv application

kaushiknsolanki — Mon, 01 Jul 2013 13:22:14 GMT

Hi,

You can run the below script to get the excel's last updated time and then store into variable and show on dashboard.

SUB DoDir (Root)

FOR Each Ext in 'qvw', 'qva', 'qvo', 'qvs'

FOR Each File in filelist (Root&'\*.' &Ext)

LOAD

'$(File)' as Name,

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

NEXT File

NEXT Ext

FOR Each Dir in dirlist (Root&'\*' )

call DoDir (Dir)

NEXT Dir

ENDSUB

CALL DoDir ('C:')

Regards,

Kaushik Solanki

Re: inserting the timestamp of data source in qv application

marksmunich — Mon, 01 Jul 2013 13:41:37 GMT

sounds good. i will try it

Re: inserting the timestamp of data source in qv application

marksmunich — Tue, 02 Jul 2013 07:21:46 GMT

could you please expain how to use this script in my application.

Re: inserting the timestamp of data source in qv application

kaushiknsolanki — Tue, 02 Jul 2013 07:30:02 GMT

Hi,

Use the same script in your application. Something like this.

SUB DoDir (Root)

FOR Each Ext in 'qvw', 'qva', 'qvo', 'qvs'

FOR Each File in filelist (Root&'\*.' &Ext)

LOAD

TEST:

'$(File)' as Name,

FileSize( '$(File)' ) as Size,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

NEXT File

NEXT Ext

FOR Each Dir in dirlist (Root&'\*' )

call DoDir (Dir)

NEXT Dir

ENDSUB

CALL DoDir ('C:')

Let vDate = Peek('FileTime',0,'TEST');

Now the last updated datetime is in variable, you can use it where ever you want.

Note: I assume that the TEST table will have only 1 record (for the file you want.)

Regards,

Kaushik Solanki