Loop through creating synthetic keys on main table

[CK_WAKE]

Hi There,

I have been tasked with making some existing changes. However, the existing loop creates synthetic keys from the MAIN_TABLE. I need your help fixing this. Below

NoConcatenate
calendar:
LOAD
Distinct
Year as [Reporting year]
FROM date.qvd
WHERE Year >='2022'and Year < Year(Today());

for _i = 0 to NoOfRows('calendar')-1
LET vFY_Year = peek('Reporting year', _i, 'calendar');

MAIN_TABLE:
LOAD
'$(vFY_Year)' as FY_YEAR_SNAPSHOT,
ID, 
COL1,
COL2,
COL3
FROM $(vQVDDATA)
(qvd)
WHERE DATE#('01-'&'07-'&'$(vFY_Year)','DD-MM-YYYY') >= START_DT AND 
DATE#('01-'&'07-'&'$(vFY_Year)','DD-MM-YYYY') <= END_DT
;
TABLE2:
LOAD
ID,
COL10,
COL11,
COL12
FROM $(vQVDDATA2)
(qvd)
WHERE DATE#('01-'&'07-'&'$(vFY_Year)','DD-MM-YYYY') >= START_DT AND 
DATE#('01-'&'07-'&'$(vFY_Year)','DD-MM-YYYY') <= END_DT
;
INNER JOIN (MAIN_TABLE)
LOAD *
Resident TABLE2;
Drop Table  TABLE2;

TABLE3:
LOAD
ID,
COL22,
COL33,
COL44
FROM $(vQVDDATA3)
(qvd)
WHERE DATE#('01-'&'07-'&'$(vFY_Year)','DD-MM-YYYY') >= START_DT AND 
DATE#('01-'&'07-'&'$(vFY_Year)','DD-MM-YYYY') <= END_DT
;
INNER JOIN (MAIN_TABLE)
LOAD *
Resident TABLE3;
Drop Table TABLE3;

SET vFY_Year = ;   
next _i
;

is the sample code I have.

[marcus_sommer]

Don't join within a loop else create there 3 separate tables with all years and afterwards  you join them against the ID and FY_YEAR.

[CK_WAKE]

Hi There,As you mentioned, I tried different ways and could not achieve it. Appreciate if you can assist with the above query. Thanks.

[marcus_sommer · Accepted solution]

It was meant in this way (more simplified):

for each v in 2022, 2023
   t1: load ID, $(v) as Year, F1, F2 from X where Year = $(v);
   t2: load ID, $(v) as Year, F4, F5 from Y where Year = $(v);
   t3: load ID, $(v) as Year, F7, F8 from Z where Year = $(v);
next

t4: load * resident t1; inner join(t4) load * resident t2; inner join(t4) load * resident t3;
drop tables t1, t2, t3;

[CK_WAKE]

Thanks for the help it worked.