topic Re: How to get the number 1 if the time span between same or different vehicle is more than 1 hour in QlikView

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

srikanth99 — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

felipedl — Wed, 18 Jul 2018 17:00:28 GMT

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;

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

srikanth99 — Thu, 19 Jul 2018 04:40:32 GMT

Thank you Felip.

It works.

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

felipedl — Thu, 19 Jul 2018 11:50:07 GMT

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

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

srikanth99 — Thu, 19 Jul 2018 13:22:47 GMT

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