Problem with intervalmatch

[andrefpc]

Hi, I'm trying to use intervalmatch to connect to tables, one with production, by time and machine and other with shifts.

The production table has a date and a timestamp for each record, the shifts table has the shift ID, start hour, end hour and machine.

Here's the script I'm using

Co_Mov:
//Events:
Load 
KEY_LINK_COD_DATA,
KEY_LINK_RECURSO,
Time(Frac(Hora_Fim/60/60)) as [Hora Produção],

Hora_Fim,
Qtd_Movimento_1
From [lib://Teste:DataFiles/Co_Mov_F1.qvd](qvd);


Dt_Turno:
//Shifts
load
KEY_LINK_RECURSO,
maketime(subfield(HIni_T1,':',1),subfield(HIni_T1,':',2),00) as HIni_T1,
maketime(subfield(HIni_T2,':',1),subfield(HIni_T2,':',2),00) as HFim_T1,
maketime(subfield(HIni_T2,':',1),subfield(HIni_T2,':',2),00) as HIni_T2,
maketime(subfield(HIni_T3,':',1),subfield(HIni_T3,':',2),00) as HFim_T2,
maketime(subfield(HIni_T3,':',1),subfield(HIni_T3,':',2),00) as HIni_T3,
maketime(subfield(HIni_T1,':',1),subfield(HIni_T1,':',2),00) as HFim_T3
;
 load
//empr_cod,
KEY_LINK_RECURSO,
Time#(tb_alfa1, 'hh:mm:ss')  as HIni_T1,
Time#(tb_alfa2, 'hh:mm:ss')  as HFim_T1,
Time#(tb_alfa2, 'hh:mm:ss')  as HIni_T2,
Time#(tb_alfa3, 'hh:mm:ss')  as HFim_T2,
Time#(tb_alfa3, 'hh:mm:ss')  as HIni_T3,
Time#(tb_alfa1, 'hh:mm:ss')  as HFim_T3
;
load 
empr_cod,
empr_cod & '|' & 'M' & '|' & tb_cod as KEY_LINK_RECURSO,
tb_alfa1,
//timestamp(tb_alfa1 + (8/24),'hh:mm') as HrFim,
TB_ALFA2 as tb_alfa2,
tb_alfa3
from [lib://Teste:DataFiles/MULTI_sb_tabvl.qvd](qvd)
WHERE tb_num = 9603;

//exit script;

 
b:
crosstable(Turno,HoraIni)
load
KEY_LINK_RECURSO,
HIni_T1 as T1,
HIni_T2 as T2,
HIni_T3 as T3
resident Dt_Turno;
//drop table Dt_Turno;


c:
crosstable(Turno,HoraFim)
load
KEY_LINK_RECURSO,
HFim_T1 as T1,
HFim_T2 as T2,
HFim_T3 as T3
resident Dt_Turno;
drop table Dt_Turno;


d:
Load
*,
1 as drop
resident b;

left join(d)
load
*
resident c;

drop table b;
drop table c;
drop field drop;

//exit script;

IntervalMatch:
IntervalMatch ([Hora Produção],KEY_LINK_RECURSO)
Load distinct HoraIni, HoraFim,KEY_LINK_RECURSO resident d;

And the output (please note that the record for the "Hora Produção" = 1:08:24PM should only be associated with Shift T1 but it's associated with all 3 shifts:

In this table I have:

KEY_LINK_RESOURCE = MachineID

Turno = Shift description

HoraIni = shift start time

HoraFim = shift end time

KEY_LINK_COD_DATA = production date

Sum(Qtd_Movimento_1) = produced quantity

Hora Produção = production time

Is it possible that the problem comes from the number formatting?

Thanks in advance.

[morgankejerhag]

I haven't gone through all the logics, but a common problem with crosstable is that all columns become text. If you are trying to match a number with text, you will get into trouble. Could that be it?