Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
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.

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

Dumb:
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_:

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:
(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 )