Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
Hi There,As you mentioned, I tried different ways and could not achieve it. Appreciate if you can assist with the above query. Thanks.
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;
Thanks for the help it worked.