topic Re: calculating difference between timestamps in QlikView

calculating difference between timestamps

Anonymous — Tue, 24 May 2016 06:48:50 GMT

i have two time stamps from Session-Session start and Audit log -Time stamp , i am trying to calculate the difference between these two time stamps .

session Start

Audit Time stamp

2016-04-29 02:43:12

2016-04-29 02:43:02

now i am using Audit timestamp- session start which is giving me

0.00011574073869269

but i need it as 10 secs ,Plese help kkkumar82‌marcus_sommer‌stalwar1‌jagan‌

Re: calculating difference between timestamps

Kushal_Chawda — Tue, 24 May 2016 06:53:12 GMT

= Second(Frac([Audit timestamp] - [session start]))

Second(Frac([Audit timestamp] - [session start])) &' secs' as Second

floor(([Audit timestamp] - [session start])*24*60*60) &' secs' as Second

Re: calculating difference between timestamps

tresB — Tue, 24 May 2016 07:00:20 GMT

May be : = Interval([Audit timestamp] - [session start], 'ss' )

Re: calculating difference between timestamps

Anonymous — Tue, 24 May 2016 07:19:06 GMT

both are working fine thanks for your quick help ,but i need little more help i want to calculate the max,min,avg of the difference as two fields are in two different tables how can i do it , as i cant take resident of two fields into single table i am using this script :

Session:

LOAD [Exe Type],

[Exe Version],

[Server Started],

Timestamp,

Document,

[Document Timestamp],

[QlikView User],

[Exit Reason],

[Session Start],

[Session Duration],

[CPU spent (s)],

[Bytes Received],

[Bytes Sent],

Calls,

Selections,

[Authenticated user],

[Identifying user] as User,

[Client machine identification],

[Serial number],

[Client Type],

[Client Build Version],

[Secure Protocol],

[Tunnel Protocol],

[Server Port],

[Client Address],

[Client Port],

[Cal Type],

[Cal Usage Count],

Session

Audit:

LOAD

Timestamp as [audit Timestamp],

// Document ,

Type,

User,

Message,

Id,

Session as [Audit Session]

Temp:

load

[Authenticated user],

[Session Start]

resident Session;

left join (Temp)

load

user,

[audit Timestamp]

resident Audit;

Data:

load

user,

[Session Start],

[audit Timestamp],

[audit Timestamp]-[Session Start] as Response

resident Temp;

tresesco

Re: calculating difference between timestamps

Kushal_Chawda — Tue, 24 May 2016 07:23:35 GMT

Either you can link both the Table on Key field and perform the calculation on front end or if you want to do the calculation in script then join both the table on Key field and perform calculation on resident load

Re: calculating difference between timestamps

Anonymous — Tue, 24 May 2016 07:33:04 GMT

the script i am using is giving me error can you help me in debugging the error

Field not found - <user>

left join (Temp)

load

user,

[audit Timestamp]

resident Audit

Field not found - <user>

Data:

load

user,

[Session Start],

[audit Timestamp],

[audit Timestamp]-[Session Start] as Response

resident Temp

Re: calculating difference between timestamps

Kushal_Chawda — Tue, 24 May 2016 07:37:53 GMT

I think the field is [QlikView User] instead of user

Re: calculating difference between timestamps

Anonymous — Tue, 24 May 2016 07:40:16 GMT

Thanks buddy , its an typo while writing the field names manually , i go it corrected and its working fine

really thanks for the quick replies appreciate your help and looking forward for more help

Re: calculating difference between timestamps

Kushal_Chawda — Tue, 24 May 2016 07:42:05 GMT

Not a problem