Skip to main content
Announcements
Meet the 2024 Qlik Luminaries and Partner Ambassadors! READ ON!
cancel
Showing results for 
Search instead for 
Did you mean: 
alexandermllr
Creator
Creator

Filter Interval of two Timestamps

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:

StartEndValue
01.01.2020 14:2001.01.2020 15:30300
01.03.2020 22:5502.03.2020 04:101450
01.01.2020 15:1001.01.2020 15:1420


Second table (calendar):

TimeStamp_StartTimeStamp_End

01.01.2020 00:00

01.01.2020 00:15
01.01.2020 00:1501.01.2020 00:30
01.01.2020 00:3001.01.2020 00:45

 

I hope I could describe it good 🙂

Thanks for your help!

Labels (3)
7 Replies
Taoufiq_Zarra

for example here what is the output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
alexandermllr
Creator
Creator
Author

The output should be:

StartEndTimeStamp_StartTimeStamp_EndValue
01.01.2020 14:2001.01.2020 15:3001.01.2020 14:1501.01.2020 14:30300
01.01.2020 14:2001.01.2020 15:3001.01.2020 14:3001.01.2020 14:45300
01.03.2020 22:5502.03.2020 04:1001.03.2020 22:4501.03.2020 23:001450
01.03.2020 22:5502.03.2020 04:1001.03.2020 23:0001.03.2020 23:151450


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

Taoufiq_Zarra

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 :

Capture.PNG

 

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
alexandermllr
Creator
Creator
Author

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.

Taoufiq_Zarra

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
alexandermllr
Creator
Creator
Author

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:

StartEndValueTimeStamp_StartTimeStamp_End
01.01.2020 15:1001.01.2020 15:142001.01.2020 15:0001.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.

Taoufiq_Zarra

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉