topic Re: How to use intervalmatch in App Development

How to use intervalmatch

BryanFontes — Tue, 19 Jul 2022 15:10:36 GMT

Hello guys,

I have to match different people with the same code with values from a certain date. I have two tables, one that contains the min-max date for a code+name (I), and another with code, date, and value (II).

In this example, the code 0001 was used for 'Alex' in 2020, assigned to 'Marie' from 2021 to May 2022, and lastly assigned to 'John'. (no overlaps in the base)

I. Min and max dates that a certain person possessed a certain code:

Cod	Cod_Name	Date_min	Date_max
0001	0001 - Alex	2020-01-01	2020-12-31
0001	0001 - Marie	2021-01-01	2022-05-01
0001	0001 - John	2022-05-02	2022-12-31

II. Values from a certain date for a certain code:

Cod	Date	Value
0001	2020-08-01	4000
0001	2021-08-01	5000

I need to bring the 'Cod_Name' for this last table (II), to achieve something like this:

Goal:

Cod	Date	Value	Cod_Name
0001	2020-08-01	4000	0001 - Alex
0001	2021-08-01	5000	0001 - John

I really don't know how to do this. If someone could help me with this task, I'd be sooo glad 🙂

Re: How to use intervalmatch

MarcoWedel — Tue, 19 Jul 2022 20:59:26 GMT

one solution might be:

table1: LOAD RecNo() as ID, * Inline [ Cod, Cod_Name, Date_min, Date_max 0001, 0001 - Alex, 2020-01-01, 2020-12-31 0001, 0001 - Marie, 2021-01-01, 2022-05-01 0001, 0001 - John, 2022-05-02, 2022-12-31 0002, 0002 - Alice, 2020-01-01, 2020-12-31 0002, 0002 - Bob, 2021-01-01, 2021-12-31 ]; table2: LOAD *, Cod as Cod2 Inline [ Cod, Date, Value 0001, 2020-08-01, 4000 0001, 2021-08-01, 5000 0002, 2020-07-01, 6000 0002, 2021-06-01, 7000 ]; tabLink: IntervalMatch(Date,Cod) LOAD Date_min, Date_max, Cod Resident table1; Left Join (tabLink) LOAD Date_min, Date_max, Cod, ID Resident table1; DROP Fields Date_min, Date_max, Cod From tabLink; DROP Field Cod From table2;

Re: How to use intervalmatch

BryanFontes — Thu, 21 Jul 2022 12:38:41 GMT

Hello Marco,

Thanks for your reply! I do think it solves the problem, but I didn't explain correctly what are my next steps after getting the result.

I need all the fields in the same table because I have to group them by Cod_Name to get the total value per date and per Cod_Name in the script. It's important to say that my database is large (40M-ish rows), so I'm looking for a way to use the minimum LEFT JOINs possible to get all the fields and group them. Do you have any idea how could I accomplish this?