Iterative Macro or Batch Macro :: loop through record

[Shank]

Hi All,

I'm facing a challenge with one of our use cases, and I was hoping someone could lend a hand in finding a solution. I've attempted to use the join method, but unfortunately, the results I'm obtaining are not as accurate as required.

I'm specifically seeking help with implementing an iterative macro to address this issue. The objective is to loop through Table B for every key in the data, and based on the date, extract the VAR column Data. To provide a clearer picture, let's consider an example: in Table 1, if Row 1 contains Key "A" and its allotted date is 1/1/2024, I need to loop through Table 2 and traverse all the rows to retrieve the appropriate VAR and preference information. 

 the data in Table 2 should be sorted in descending order of the end date, with preference based on the lowest to highest.

Your assistance and support in achieving this would be immensely valuable.

Many thanks in advance for your help and support.

My Data set is little, huge and i have many such combination of table 2

Table 1

Rowid	KeyID	Allot Date
1	A	1/1/2024
2	A	1/6/2026
3	C	1/3/2024
4	D	1/4/2024
5	E	1/5/2024
6	F	1/6/2024
7	G	1/7/2024
8	H	1/8/2024
9	I	1/9/2024
10	J	1/10/2024

Table 2

KeyID	Start	End	perference	Var
A	1/1/2023	1/2/2024	100	AA
A	1/3/2024	1/1/2025	100	AB
A	1/1/2025	1/1/2026	100	AC
A	1/1/1999	12/31/2099	500	AD
A	1/1/1999	12/31/2099	500	AE
A	1/1/1999	12/31/2099	500	AE
A	1/1/2026	11/11/2029	799	AA

Expected Output

Rowid	KeyID	Allot Date	perference	Var
1	A	1/1/2024	100	AA
2	A	1/6/2026	500	AD

[SPetrie]

I believe a batch macro would be more appropriate in terms of a macro vs an iterative.

I think a dynamic replace is more efficient compute wise over large data sets though. (I could be wrong about that).

I have an example of both attached. In both cases, the dates need to be converted to actual date values in order to do the comparisons. Hope this helps.