Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Ayoub
Contributor III
Contributor III

Interval match help

Hello Community, 

i'm working on a new project for my company and i need your help. i want to match time extracted from a field into an interval of time.
the code who makes intervals work as a charm, and the extraction code also. but my problem is on interval match time.
I really need help on this. here's below the code i wrote. 

and thank u in advance

//****************************************************** Période ****************************************\\
Time:
LOAD * INLINE [
Start, Finish
09:00, 13:00
14:00, 18:00
];

Dumb:
LOAD *,'$(Periode)' as vPeriode
INLINE [Dumb,
0];

LET P=if(FieldValue('vPeriode', 1)='15 Minutes',96,if(FieldValue('vPeriode', 1)='30 Minutes',48,if(FieldValue('vPeriode', 1)='1 H',24,if(FieldValue('vPeriode', 1)='2 H',12))));

Time_:
Load *,

Time(RangeMax(Start, Floor(Start,1/$(P))+(iterno()-1)/$(P)),'hh:mm') as NewStart,

if(Time(RangeMin(Finish, Floor(Start,1/$(P))+iterno()/$(P))-Time('00:01','hh:mm'),'hh:mm')=time#(time('17:59','hh:mm')),time#('18:00','hh:mm'),
if(Time(RangeMin(Finish, Floor(Start,1/$(P))+iterno()/$(P))-Time('00:01','hh:mm'),'hh:mm')=time#(time('12:59','hh:mm')),time#('13:00','hh:mm'),
Time(RangeMin(Finish, Floor(Start,1/$(P))+iterno()/$(P))-Time('00:01','hh:mm'),'hh:mm'))) as NewFinish

Resident Time

While Start+(iterno()-1)/$(P) < Finish;

DROP Table Time;
//**************************************************************************************************************\\

OctContH:
LOAD ID,
(date(DATEHISTORIQUE, 'hh:mm')) as Time,
date(DATEHISTORIQUE,'DD/MM/YYYY hh:mm:ss') as DATEHISTORIQUE
Where not match(WeekDay(DATEEVENEMENT),'dim.')
and (not(Match(WeekDay(DATEEVENEMENT),'sam.') and time#(time(DATEEVENEMENT, 'hh:mm:ss.fff' )) >= time#(time( '15:00:00.000' , 'hh:mm:ss.fff' ))))
and not Match(date(DATEEVENEMENT,'DD/MM/YYYY'),$(vFiltre));

SQL SELECT ID,
DATEEVENEMENT,
DATEHISTORIQUE,

FROM OCT

where DATEEVENEMENT BETWEEN

TO_DATE('$(vInputDate_du)', 'DD/MM/YYYY') AND TO_DATE('$(vInputDate_au)', 'DD/MM/YYYY');

Inner Join IntervalMatch ( Time )
LOAD
NewStart ,
NewFinish
Resident Time_;

DROP Table Time_;

 

0 Replies