topic Re: Script start time and end time in QlikView

Script start time and end time

ajaykumar1 — Wed, 29 Jul 2015 13:58:52 GMT

Hi Everyone,

I need some one help to get the below requirement.

I need to get the script start time(system time) and end time(system time), and the below format should store into QVD.Also i need to maintain the history of the Start time and End Time daily(i.e. It shoud not override the data)

Task Name	StartTime	End Time
Reload_Sales	29/07/2015 11:20:20 AM	29/07/2015 12:40:00 PM

I wrote the following script But am unable to get the Start and End time in same above format.

Start:

LOAD *, now() as Date

INLINE [

Task Name, Action

Reload_Sales, Start Time

] ;

Load A,

FROM xxxx.qvd;

Start:

LOAD *, now() as Date

INLINE [

TAsk Name, Action

Reload_Sales, End Time

] ;

Store Start into time.qvd;

Please correct me where am wrong to get the start and end time in the above mentioned format.

More Thanks in advance,

Ajay

Re: Script start time and end time

marcus_sommer — Wed, 29 Jul 2015 14:03:11 GMT

I think you will find here all what you want: https://community.qlik.com/docs/DOC-7177#comment-20906

- Marcus

Re: Script start time and end time

ajaykumar1 — Wed, 29 Jul 2015 17:35:05 GMT

Thanks Marcus.But i want to store the start time and end time in a qvd.So based on the qvd i will compare my day on day how much time qvds are taking to store.

I hope my requirement is clear.

More Thanks,

Ajay

Re: Script start time and end time

maxgro — Wed, 29 Jul 2015 17:56:57 GMT

Start: load * from time.qvd (qvd);

LOAD *, Timestamp(now()) as Date

INLINE [

Task Name, Action

Reload_Sales, Start Time

] ;

Store Start into time.qvd;

// ............

LOAD *

FROM a11.qvd

(qvd);

Start:

LOAD *, Timestamp(now()) as Date

INLINE [

TAsk Name, Action

Reload_Sales, End Time

] ;

Start: load * from time.qvd (qvd);

LOAD *, date(now()) as Date

INLINE [

Task Name, Action

Reload_Sales, End Time

] ;

Store Start into time.qvd;

Re: Script start time and end time

ajaykumar1 — Wed, 29 Jul 2015 18:04:03 GMT

Thanks Massimo.

The first line;

start: load * from time.qvd(qvd)?

From where i need to get this initially?

if possible plz add any sample qvw file.

Thanks,

Ajay

Re: Script start time and end time

maxgro — Wed, 29 Jul 2015 18:27:05 GMT

you have to check if the file exists

Re: check if qvd file already exist or not

if the file doesn't exist, skip the first load

Re: Script start time and end time

ajaykumar1 — Wed, 29 Jul 2015 18:31:23 GMT

Ok...I will check above script tomorrow and get back.

Thanks for your time.

Ajay

Re: Script start time and end time

marcus_sommer — Wed, 29 Jul 2015 19:17:47 GMT

If you looked through the post and all comments you will find a solution with various additionally informations about the last load to store in a qvd.

- Marcus.

Re: Script start time and end time

ajaykumar1 — Thu, 30 Jul 2015 11:27:19 GMT

Thanks Massimo,

I have checked and executed your script, and getting following output.

Job Name	Action	Date
Reload_Sales	Start time	7/29/2015 11:52:05 AM
Reload_Sales	Start time	7/29/2015 12:30:46 AM
Reload_Sales	End time	7/29/2015 12:52:05 PM
Reload_Sales	End time	7/29/2015 1:30:46 AM

But, I need output like below format in QVD.

Job Name	Start time	End time
Reload_Sales	7/29/2015 11:52:05 AM	7/29/2015 12:52:05 PM
Reload_Sales	7/29/2015 12:30:46 AM	7/29/2015 1:30:46 AM

Thanks

Ajay

Re: Script start time and end time

ajaykumar1 — Fri, 31 Jul 2015 08:43:14 GMT

Hi All,

Any help from any one.

Thanks

Ajay

Re: Script start time and end time

jagan — Sat, 01 Aug 2015 10:01:49 GMT

HI,

Try like this

LET vStartTime = Now();

Data:

LOAD

FROM Data.csv;

LET vEndTime = Now();

ReloadDetails:

LOAD

'Data Table' AS ReloadTable,

'$(vStartTime)' AS StartTime,

'$(vEndTime)' AS EndTime

AutoGenerate (1);

LET vStartTime = Now();

Data1:

LOAD

FROM Data1.csv;

LET vEndTime = Now();

Concatenate(ReloadDetails):

LOAD

'Data1 Table' AS ReloadTable,

'$(vStartTime)' AS StartTime,

'$(vEndTime)' AS EndTime

AutoGenerate (1);

// Add new records to QVD

IF Alt(FileSize('c:\Somepath\ReloadDetails.qvd'), 0) > 0 THEN

Concatenate(ReloadDetails)

LOAD

FROM ReloadDetails.qvd (qvd);

ENDIF;

STORE ReloadDetails INTO ReloadDetails.qvd;

Hope this helps you.

Regards,

Jagan.

Re: Script start time and end time

qlikviewwizard — Sat, 01 Aug 2015 15:46:52 GMT

Hi ajaykumar1

Please close the thread if you get the answer. Thank you.

Re: Script start time and end time

maxgro — Sat, 01 Aug 2015 19:08:07 GMT

let vStartTime=Timestamp(now());

trace vStartTime=$(vStartTime);

SLEEP(1000);

if FileSize('time.qvd')>0 then

time: load * from time.qvd (qvd);

ENDIF;

time:

LOAD

'Reload_Sales' as TaskName,

'$(vStartTime)' as StartTime,

Timestamp(now()) as EndTime

AutoGenerate 1;

Store time into time.qvd;

Re: Script start time and end time

ajaykumar1 — Mon, 03 Aug 2015 11:00:52 GMT

Thanks Jagan.Its working perfectly as earlier asked.

One more thing; is this script stores the start time if in case the script hanged(like without completion of the task).

It means if in case before completion of the QVD if the task hangs whether start time will capture ?

or when only completion of the task then only both start time and end time is will capture?

More Thanks,

Ajay

Re: Script start time and end time

ajaykumar1 — Mon, 03 Aug 2015 11:04:18 GMT

Hi Qlikviewwizard,

I will definitely close the thread ,that will be helpful for others whoever required.

Sorry if in case any convenience caused to you 🙂

Regards,

Ajay

Re: Script start time and end time

jagan — Mon, 03 Aug 2015 11:06:45 GMT

Hi,

Before storing the QVD if your reload fails it won't store in QVD, the start time and end time will be in the variables and once the task finished the data is stored in the QVD.

If you got the answer please close this thread by giving Correct and Helpful Answers to the posts which helps you.

Regards,

Jagan.

Re: Script start time and end time

ajaykumar1 — Mon, 03 Aug 2015 11:09:56 GMT

Thanks Jagan for your clarification.

Re: Script start time and end time

Anonymous — Mon, 03 Aug 2015 11:15:16 GMT

Try This

Let Vmin = Timestamp(now());

Data:

LOAD

FROM Data.csv;

Let Vmax = Timestamp(now());

Time:
Load * INLINE [
Reload, Start, End
'Relaod_Sales',$(Vmin),$(Vmax)
];

Re: Script start time and end time

Anonymous — Mon, 03 Aug 2015 11:19:12 GMT

Let Vmin = Timestamp(now());

Table:
LOAD *
FROM
[sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Let Vmax = Timestamp(now());

Time:
Load * INLINE [
Reload, Start, End
'Relaod_Sales',$(Vmin),$(Vmax)
];

OldTime:
LOAD Reload,
Start,
End
FROM

(qvd);

STORE Time into 'C:\Users\manoj_nair09\Downloads\Time.qvd'(qvd);

DROP TABLE Table;