Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
abhaysingh
Specialist II
Specialist II

Time Interval Help

hi Guys,

I have one scenario in which i getting data every second a day

i.e. 25-Sep-15 12:00:01 AM

     25-Sep-15 12:00:02 AM

     25-Sep-15 12:00:03 AM

     25-Sep-15 12:00:04 AM

     25-Sep-15 12:00:05 AM

     25-Sep-15 12:00:06 AM

      so on...

     25-Sep-15 01:00:01 AM

     25-Sep-15 01:00:02 AM

     25-Sep-15 01:00:03 AM

     25-Sep-15 01:00:04 AM

I want i to extract intervals between then like below

    

     25-Sep-15 02:00:01 AM

     25-Sep-15 01:00:01 AM

     25-Sep-15 03:00:01 AM

     25-Sep-15 04:00:01 AM

     25-Sep-15 05:00:01 AM


so on


Any Suggestion??



17 Replies
SreeniJD
Specialist
Specialist

Abhay - Can you please give us more clarity?

Sreeni

abhaysingh
Specialist II
Specialist II
Author

this is the output i want from above data

     25-Sep-15 02:00:01 AM

     25-Sep-15 01:00:01 AM

     25-Sep-15 03:00:01 AM

     25-Sep-15 04:00:01 AM

     25-Sep-15 05:00:01 AM


regards

Abhay

jonathandienst
Partner
Partner

You could use something like

Load TimeStamp,

  ...

FROM ...

Where Mod(Frac(TimeStamp) * 24*60*60, 3600) = 1

OR

Load TimeStamp,

  ...

FROM ...

Where Round(Mod(Frac(TimeStamp) * 24*60*60, 3600)) = 1

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner
Partner

t1.png

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
abhaysingh
Specialist II
Specialist II
Author

Hi Jonathan,

thanks for revert.. but not getting output..

sasiparupudi1
Master III
Master III

Try like this

Data:

LOAD ts,Timestamp#(ts,'DD-MMM-YYYY hh:mm:ss TT') as ts1,

time(Timestamp#(ts,'DD-MMM-YYYY hh:mm:ss TT'),'s') as tstimeinSec

INLINE [

ts

25-Sep-15 12:00:01 AM

     25-Sep-15 12:00:02 AM

     25-Sep-15 12:00:03 AM

     25-Sep-15 12:00:04 AM

     25-Sep-15 12:00:05 AM

     25-Sep-15 12:00:06 AM

     25-Sep-15 01:00:01 AM

     25-Sep-15 01:00:02 AM

     25-Sep-15 01:00:03 AM

     25-Sep-15 01:00:04 AM

    

     25-Sep-15 02:00:01 AM

     25-Sep-15 02:00:02 AM

    

          25-Sep-15 03:00:01 AM

     25-Sep-15 03:00:02 AM

];

NoConcatenate

Final:

load * Resident Data

where num(trim(tstimeinSec))=1;

drop Table Data;

maxgro
MVP
MVP

Data:

LOAD

  ts,

  Timestamp#(ts,'DD-MMM-YYYY hh:mm:ss TT') as ts1

INLINE [

  ts

     25-Sep-15 12:00:01 AM

     25-Sep-15 12:00:02 AM

     25-Sep-15 12:00:03 AM

     25-Sep-15 12:00:04 AM

     25-Sep-15 12:00:05 AM

     25-Sep-15 12:00:06 AM

     25-Sep-15 01:00:01 AM

     25-Sep-15 01:00:02 AM

     25-Sep-15 01:00:03 AM

     25-Sep-15 01:00:04 AM

     25-Sep-15 02:00:01 AM

     25-Sep-15 02:00:02 AM

     25-Sep-15 03:00:01 AM

     25-Sep-15 03:00:02 AM

];

NoConcatenate

Final:

load

  *,

  floor(ts1, 1/24)

Resident Data

Where floor(ts1, 1/24) <> floor(peek(ts1), 1/24) 

order by ts1;

drop Table Data;

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_182659_Pic1.JPG

LOAD *,

    Timestamp(Floor(YourTimeStamp,'01:00:00','00:00:01'),'DD-MMM-YY hh:mm:ss TT') as YourIntervalStart;

LOAD Timestamp(Today()-2+IterNo()/86400,'DD-MMM-YY hh:mm:ss TT') as YourTimeStamp,

    Ceil(Rand()*1000) as fact

AutoGenerate 1

While Today()-2+IterNo()/86400<=Now();

hope this helps

regards

Marco

abhaysingh
Specialist II
Specialist II
Author

Hi Marco,

Need Script help for this,,

i have scenario like below..

Load

     Field1,

     DateRecorded,

     field2

From Source...

Where to write Above script;

Thanks

Abhay