topic Re: Time Interval Help in QlikView

Time Interval Help

abhaysingh — Fri, 25 Sep 2015 10:07:10 GMT

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??

Re: Time Interval Help

SreeniJD — Fri, 25 Sep 2015 10:11:18 GMT

Abhay - Can you please give us more clarity?

Sreeni

Re: Time Interval Help

abhaysingh — Fri, 25 Sep 2015 10:12:49 GMT

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

Re: Time Interval Help

jonathandienst — Fri, 25 Sep 2015 10:20:42 GMT

You could use something like

Load TimeStamp,

...

FROM ...

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

Load TimeStamp,

...

FROM ...

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

Re: Time Interval Help

jonathandienst — Fri, 25 Sep 2015 10:24:13 GMT

Re: Time Interval Help

abhaysingh — Fri, 25 Sep 2015 10:37:33 GMT

Hi Jonathan,

thanks for revert.. but not getting output..

Re: Time Interval Help

sasiparupudi1 — Fri, 25 Sep 2015 11:19:20 GMT

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 [

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;

Re: Time Interval Help

maxgro — Fri, 25 Sep 2015 17:36:13 GMT

Data:

LOAD

ts,

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

INLINE [

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;

Re: Time Interval Help

MarcoWedel — Fri, 25 Sep 2015 23:33:33 GMT

Hi,

one solution could be:

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

Re: Time Interval Help

abhaysingh — Tue, 29 Sep 2015 05:58:50 GMT

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

Re: Time Interval Help

abhaysingh — Tue, 29 Sep 2015 06:56:20 GMT

Please Revert...

Re: Time Interval Help

SreeniJD — Tue, 29 Sep 2015 07:00:33 GMT

Hi Abhay,

As you may be aware that you can load your table first, and you can then add the script suggested by Marco with Resident table option.

HTH,

Sreeni

Re: Time Interval Help

abhaysingh — Tue, 29 Sep 2015 07:00:35 GMT

I tried in that way...

TxnTable:

Load

Field1,

DateRecorded,

Temperature

From Source...

Concatenate(TxnTable)

Tran_Temp2:

LOAD *,

Timestamp(Floor(DateRecorded,'01:00:00','00:00:01'),'DD-MM-YY hh:mm:ss TT') as DateRecorded_Test,

Timestamp(Floor(DateRecorded,'01:00:00','00:00:01'),'DD-MM-YY hh:mm:ss TT') as TimeInterval;

LOAD

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

Ceil(Rand()*1000) as "fact"

AutoGenerate 1

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

With respect to DateRecorded_Test, I am not Getting Temprature..

Please Suggest

Abhay

Re: Time Interval Help

jonathandienst — Tue, 29 Sep 2015 07:48:14 GMT

Its not clear what you are trying to do. Do you want to filter the TxnTable to get one reading per minute? If you supply some input data and the expected output, it might help.

Re: Time Interval Help

abhaysingh — Tue, 29 Sep 2015 08:35:02 GMT

Hey Jonathan,

Pls find the sample data...

i m getting Date with time stamp for every second..

I need the output like Interval Time Below

12:00:00 AM

01:00:00 AM

02:00:00 AM

and temperature w.r.t Interval Time..

Pls Suggest

Abhay

Re: Time Interval Help

abhaysingh — Tue, 29 Sep 2015 12:43:47 GMT

Pls Revert..

Re: Time Interval Help

PrashantSangle — Wed, 30 Sep 2015 06:31:42 GMT

Hi,

Try

Timestamp(Date#(Date,'DD.MM.YYYY hh:mm:ss'),'DD/MM/YYYY hh:00:00 TT')

Regards

Re: Time Interval Help

jonathandienst — Wed, 30 Sep 2015 06:50:05 GMT

Your times in your sample do not cover every second, but to load your sample:

LOAD Id,

Date(Floor(Date#(Date, 'dd.MM.yyyy hh:mm:ss'))) As Date, //convert to pure date

Time(Frac(Date#(Date, 'dd.MM.yyyy hh:mm:ss')), 'hh:mm:ss TT') As Time, //convert to time in format requested

Num(Num#(Temperature, '0,0', ',')) As Temperature //read this as a number

FROM

(biff, embedded labels, table is Sheet1$);

Still not clear what you want to do this.