Problem with intervalmatch

andrefpc — Wed, 07 Aug 2024 12:05:03 GMT

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.

Re: Problem with intervalmatch

morgankejerhag — Wed, 12 Feb 2025 20:33:26 GMT

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?

topic Problem with intervalmatch in App Development

Problem with intervalmatch

Re: Problem with intervalmatch