Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Kushal_Chawda

= 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

View solution in original post

8 Replies
Kushal_Chawda

= 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

tresesco
MVP
MVP

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

Anonymous
Not applicable
Author

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

Kushal_Chawda

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

Anonymous
Not applicable
Author

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

Kushal_Chawda

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

Anonymous
Not applicable
Author

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

Kushal_Chawda

Not a problem