While 20 minutes of diference

[AStuckrath_CL]

Hello,

I have a issue with these data, i need to create Cumple? field, the rules is with the same id_vehiculo, with different CodTransito and PuntoCobro, the difference between fechahora(1) and fechahora(2) is <= 20 minutes

How can i do these?

CodTransito	ID Vehiculo	PuntoCobro	FechaHora	Cumple?
20056995	V2	201	11-03-2024 08:57:14	No
20063584	V2	101	11-03-2024 12:23:42	No
20072249	V2	201	11-03-2024 16:33:01	Yes
20072418	V2	101	11-03-2024 16:37:43	Yes
20092881	V2	201	12-03-2024 09:55:24	No
20099617	V2	101	12-03-2024 13:30:57	No
20110838	V2	201	12-03-2024 18:42:12	Sí
20110960	V2	101	12-03-2024 18:46:33	Sí

[AStuckrath_CL]

help-ayuda

[steeefan]

Try this:

Data:
NOCONCATENATE LOAD
  CodTransito,
  [ID Vehiculo],
  PuntoCobro,
  Timestamp(Timestamp#(FechaHora, 'DD-MM-YYYY hh:mm:ss')) AS FechaHora;
LOAD * INLINE [
  CodTransito,ID Vehiculo,PuntoCobro,FechaHora
  20056995,V2,201,11-03-2024 08:57:14
  20063584,V2,101,11-03-2024 12:23:42
  20072249,V2,201,11-03-2024 16:33:01
  20072418,V2,101,11-03-2024 16:37:43
  20092881,V2,201,12-03-2024 09:55:24
  20099617,V2,101,12-03-2024 13:30:57
  20110838,V2,201,12-03-2024 18:42:12
  20110960,V2,101,12-03-2024 18:46:33
];

Data2:
NOCONCATENATE LOAD
  *,
  If([ID Vehiculo] = Previous([ID Vehiculo]) AND CodTransito <> Previous(CodTransito) AND PuntoCobro <> Previous(PuntoCobro),
      If(FechaHora - Previous(FechaHora) <= MakeTime(0,20,0),
      'Sì',
      'No'
    ),
    'No'
  ) AS Cumple
RESIDENT
  Data
ORDER BY
  CodTransito ASC;

DROP TABLE Data;

It will however only flag the second entry as 'Sì', for the first one you'd have to add another check.