Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qvhelpplease
New Contributor II

calculating difference between timestamps

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:122016-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 kkkumar82marcus_sommerstalwar1jagan

1 Solution

Accepted Solutions

Re: calculating difference between timestamps

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

or

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


or


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

8 Replies

Re: calculating difference between timestamps

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

or

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


or


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

MVP
MVP

Re: calculating difference between timestamps

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

qvhelpplease
New Contributor II

Re: calculating difference between timestamps

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

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

qvhelpplease
New Contributor II

Re: calculating difference between timestamps

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

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

qvhelpplease
New Contributor II

Re: calculating difference between timestamps

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

Not a problem

Community Browser