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: 
srikanth99
Contributor III
Contributor III

How to get the number 1 if the time span between same or different vehicle is more than 1 hour

Hi,

      i should get the value 1 in seperate column if the time is 1 hour difference between same or different vehicles and empty if diff is less than 1 hour. There more than 1 lakh data in the table.

Capture.PNG

1 Solution

Accepted Solutions
srikanth99
Contributor III
Contributor III
Author

Thank you Felip.

It works.

View solution in original post

4 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi,

Do something like this:

data:

Load

     [Vehicle No],

     [Date & Time]

     //OTher fields here too

From [Whatever];

_tmpField:

load

     [Vehicle No],

     [Date & Time],

     if

     (

          peek([Vehicle No]=[Vehicle No],

          if

          (

               [Date & Time]-peek([Date & Time])>1,

               1,

               0

          )   

     ) as [Flag Difference bigger then 1]

Resident data

order by

     [Vehicle No],

     [Date & Time]

;

left join (data)

Load *

Resident _tmpField;


drop table _tmpField;

srikanth99
Contributor III
Contributor III
Author

Thank you Felip.

It works.

felipedl
Partner - Specialist III
Partner - Specialist III

Can't you mark my answer as correct since it helped you?

srikanth99
Contributor III
Contributor III
Author

we should do it in front end reporting in straight table by loading the excel file