calculating difference between timestamps

[Anonymous]

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‌

[Kushal_Chawda · Accepted solution]

= 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]

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

[Anonymous]

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]

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]

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