calculation base on an interval join

zarmoton — Sun, 04 Aug 2024 12:57:51 GMT

Table : TT

TT_ID

TT_START_DATE

TT_END_DATE

SITE_ID

01/07/2024

10/07/2024

01/07/2024

10/07/2024

01/07/2024

10/07/2024

05/07/2024

15/07/2024

05/07/2024

15/07/2024

Table : TSR

FCT_DATE

SITE_ID

DWH_CALL_ID

28/06/2024

123

01/07/2024

234

05/07/2024

345

04/07/2027

456

02/07/2024

567

How to right a script who calculate the column Nb CALL_ID, where the join is based on TT_ID and intervall on the date

TT_ID

TT_START_DATE

TT_END_DATE

SITE_ID

Nb CALL_ID

01/07/2024

10/07/2024

01/07/2024

10/07/2024

01/07/2024

10/07/2024

05/07/2024

15/07/2024

05/07/2024

15/07/2024

Re: calculation base on an interval join

Vegar — Mon, 05 Aug 2024 08:55:18 GMT

You can use IntervalMatch with a keyfield (SITE_ID) . It would look something like the sample script below.

SET DateFormat='DD/MM/YYYY'; //Ensuring date format identical to how it is in the source table.
TT:
LOAD * inline [
TT_ID, TT_START_DATE, TT_END_DATE, SITE_ID,
1, 01/07/2024, 10/07/2024, A,
1, 01/07/2024, 10/07/2024, B
1, 01/07/2024, 10/07/2024, C
2, 05/07/2024, 15/07/2024, A
2, 05/07/2024, 15/07/2024, D
];

TSR:
LOAD * inline [
FCT_DATE, SITE_ID, DWH_CALL_ID
28/06/2024, A, 123
01/07/2024, A, 234
05/07/2024, A, 345
04/07/2027, D, 456
02/07/2024, B, 567];

LEFT JOIN (TSR)
IntervalMatch (FCT_DATE, SITE_ID)
LOAD TT_START_DATE, TT_END_DATE, SITE_ID
RESIDENT TT;

LEFT JOIN (TT) LOAD * RESIDENT TSR;
DROP TABLE TSR;

(Note that the DWH_CALL_ID = 123 will get lost as it is not within a defined interval.)

topic Re: calculation base on an interval join in App Development

calculation base on an interval join

Re: calculation base on an interval join