topic Re: Filter Interval of two Timestamps in QlikView

Filter Interval of two Timestamps

alexandermllr — Sat, 16 Nov 2024 18:25:13 GMT

Hello everyone,

I have one table with two timestamps, Start and End.
I also greated a calendar with 15 minutes timestamps, TimeStamp_Start and TimeStamp_End.
I want to get only the 15 minutes Timestamps, when it is between the Start and End.

First table:

Start	End	Value
01.01.2020 14:20	01.01.2020 15:30	300
01.03.2020 22:55	02.03.2020 04:10	1450
01.01.2020 15:10	01.01.2020 15:14	20

Second table (calendar):

TimeStamp_Start	TimeStamp_End
01.01.2020 00:00	01.01.2020 00:15
01.01.2020 00:15	01.01.2020 00:30
01.01.2020 00:30	01.01.2020 00:45

I hope I could describe it good 🙂

Thanks for your help!

Re: Filter Interval of two Timestamps

Taoufiq_Zarra — Wed, 01 Jul 2020 14:02:47 GMT

for example here what is the output ?

Re: Filter Interval of two Timestamps

alexandermllr — Wed, 01 Jul 2020 14:12:19 GMT

The output should be:

Start	End	TimeStamp_Start	TimeStamp_End	Value
01.01.2020 14:20	01.01.2020 15:30	01.01.2020 14:15	01.01.2020 14:30	300
01.01.2020 14:20	01.01.2020 15:30	01.01.2020 14:30	01.01.2020 14:45	300
01.03.2020 22:55	02.03.2020 04:10	01.03.2020 22:45	01.03.2020 23:00	1450
01.03.2020 22:55	02.03.2020 04:10	01.03.2020 23:00	01.03.2020 23:15	1450

So for each 15 min TimeStamp Interval a new row, when it is between Start and End.

Re: Filter Interval of two Timestamps

Taoufiq_Zarra — Wed, 01 Jul 2020 19:35:42 GMT

it's a beautiful challenge

Script :

Firsttable: LOAD num(TimeStamp(TimeStamp#(Start,'DD.MM.YYYY hh:mm'))) as nStart,num(TimeStamp(TimeStamp#(End,'DD.MM.YYYY hh:mm'))) as nEnd, Div(floor((num(TimeStamp(TimeStamp#(End,'DD.MM.YYYY hh:mm')))-num(TimeStamp(TimeStamp#(Start,'DD.MM.YYYY hh:mm'))))*1440),15) as Valuetmp,* INLINE [ Start, End, Value 01.01.2020 14:20, 01.01.2020 15:30, 300 01.03.2020 22:55, 02.03.2020 04:10, 1450 01.01.2020 15:10, 01.01.2020 15:14, 20 ]; Let NumRows=NoOfRows('Firsttable'); FOR i=0 to $(NumRows)-1 LET vStart=Peek('Start',$(i),'Firsttable'); LET vEnd=peek('End',$(i),'Firsttable'); LET vnStart=Peek('nStart',$(i),'Firsttable'); LET vnEnd=peek('nEnd',$(i),'Firsttable'); LET v0=0; LET v15=peek('Valuetmp',$(i),'Firsttable'); LET vValue=peek('Value',$(i),'Firsttable'); if $(v15)<>0 then Input: load '$(vStart)' as A,'$(vEnd)' as B,'$(vValue)' as C,IterNo() as Idt,if(IterNo()=1,TimeStamp(TimeStamp#('$(vStart)','DD.MM.YYYY hh:mm'))) as D AutoGenerate 1 While $(v0) + IterNo() -1 <= $(v15); endif NEXT; Tmp: noconcatenate load A as Start,B as End,C as Value,If(Idt=1, D, TimeStamp(Peek(ValueInreval) + Time#(15,'mm') ,'DD.MM.YYYY hh:mm:ss')) as ValueInreval resident Input; drop table Input,Firsttable; Second: LOAD TimeStamp(TimeStamp#(TimeStamp_Start,'DD.MM.YYYY hh:mm')) as TimeStamp_Start,TimeStamp(TimeStamp#(TimeStamp_End,'DD.MM.YYYY hh:mm')) as TimeStamp_End INLINE [ TimeStamp_Start, TimeStamp_End 01.01.2020 14:15, 01.01.2020 14:30 01.01.2020 14:30, 01.01.2020 14:45 01.01.2020 14:45, 01.01.2020 15:15 01.01.2020 14:15, 01.01.2020 15:45 01.01.2020 14:30, 01.01.2020 14:45 01.03.2020 22:45, 01.03.2020 23:00 01.03.2020 23:00, 01.03.2020 23:15 ]; inner Join IntervalMatch ( ValueInreval ) LOAD TimeStamp_Start, TimeStamp_End Resident Second;

output :

if the Second table is filled the null values will disappear.

Re: Filter Interval of two Timestamps

alexandermllr — Thu, 02 Jul 2020 07:13:56 GMT

Thank you so much!

I tried your script and it works, except for the line:

01.01.2020 15:10, 01.01.2020 15:14, 20

This line will not appear.

Re: Filter Interval of two Timestamps

Taoufiq_Zarra — Thu, 02 Jul 2020 07:52:22 GMT

can you share a sample data with this line (FirstTable and the second ) ?

Re: Filter Interval of two Timestamps

alexandermllr — Thu, 02 Jul 2020 08:17:40 GMT

The second table is a created calendar for every 15 minutes.

In the first table the data is:

01.01.2020 15:10, 01.01.2020 15:14, 20

The output should be:

Start	End	Value	TimeStamp_Start	TimeStamp_End
01.01.2020 15:10	01.01.2020 15:14	20	01.01.2020 15:00	01.01.2020 15:15

I think the reason why it does not work is because there is only one 15 min interval for this data.

Re: Filter Interval of two Timestamps

Taoufiq_Zarra — Thu, 02 Jul 2020 08:26:15 GMT

New version :

Firsttable: LOAD num(TimeStamp(TimeStamp#(Start,'DD.MM.YYYY hh:mm'))) as nStart,num(TimeStamp(TimeStamp#(End,'DD.MM.YYYY hh:mm'))) as nEnd, Div(floor((num(TimeStamp(TimeStamp#(End,'DD.MM.YYYY hh:mm')))-num(TimeStamp(TimeStamp#(Start,'DD.MM.YYYY hh:mm'))))*1440),15) as Valuetmp,* INLINE [ Start, End, Value 01.01.2020 14:20, 01.01.2020 15:30, 300 01.03.2020 22:55, 02.03.2020 04:10, 1450 01.01.2020 15:10, 01.01.2020 15:14, 20 ]; Let NumRows=NoOfRows('Firsttable'); FOR i=0 to $(NumRows)-1 LET vStart=Peek('Start',$(i),'Firsttable'); LET vEnd=peek('End',$(i),'Firsttable'); LET vnStart=Peek('nStart',$(i),'Firsttable'); LET vnEnd=peek('nEnd',$(i),'Firsttable'); LET v0=0; LET v15=peek('Valuetmp',$(i),'Firsttable'); LET vValue=peek('Value',$(i),'Firsttable'); Input: load '$(vStart)' as A,'$(vEnd)' as B,'$(vValue)' as C,IterNo() as Idt,if(IterNo()=1,TimeStamp(TimeStamp#('$(vStart)','DD.MM.YYYY hh:mm'))) as D AutoGenerate 1 While $(v0) + IterNo() -1 <= $(v15); NEXT; Tmp: noconcatenate load A as Start,B as End,C as Value,If(Idt=1, D, TimeStamp(Peek(ValueInreval) + Time#(15,'mm') ,'DD.MM.YYYY hh:mm:ss')) as ValueInreval resident Input; drop table Input,Firsttable; Second: LOAD TimeStamp(TimeStamp#(TimeStamp_Start,'DD.MM.YYYY hh:mm')) as TimeStamp_Start,TimeStamp(TimeStamp#(TimeStamp_End,'DD.MM.YYYY hh:mm')) as TimeStamp_End INLINE [ TimeStamp_Start, TimeStamp_End 01.01.2020 14:15, 01.01.2020 14:30 01.01.2020 14:30, 01.01.2020 14:45 01.01.2020 14:45, 01.01.2020 15:00 01.01.2020 15:00, 01.01.2020 15:15 01.01.2020 15:15, 01.01.2020 15:30 01.03.2020 22:45, 01.03.2020 23:00 01.03.2020 23:00, 01.03.2020 23:15 ]; inner Join IntervalMatch ( ValueInreval ) LOAD TimeStamp_Start, TimeStamp_End Resident Second;

output :