Hi There,
I have a QVD load that takes the SNP shot of each FY year. However, when the loop is complete, I can see there are a lot of synthetic keys. I would appreciate it if you could help me fix the synthetic key issue.
The code is provided below.
NoConcatenate
DATELIST:
LOAD
Distinct FISC_PRD_NAM as [Reporting FY Period]
FROM DATE.qvd](qvd)
WHERE FISC_PRD_NAM >= '2020'
and FISC_PRD_NAM < Year(Today());
for _i = 0 to NoOfRows('DATELIST')-1
LET vYear_Current = peek('Reporting FY Period', _i, 'DATELIST');
DATA1_EXT:
LOAD
ID,
A,
B
FROM $(vQVDDATA1)
(qvd);
DATA1:
NoConcatenate
LOAD
'$(vYear_Current)' as FY_YEAR_SNAPSHOT,
ID,
A,
B
RESIDENT DATA1_EXT
WHERE not isnull(ID)
;
DROP TABLE DATA1_EXT;
DATA2_EXT:
LOAD
ID,
X,
Z,
EFEC_STRT_DT,
EFEC_END_DT
FROM $(vQVDDATA2) (qvd);
DATA2:
NoConcatenate
LOAD
ID,
X,
Z,
WHERE
DATE('01-07-$(vYear_Current)','DD-MM-YYYY') >= EFEC_STRT_DT AND
DATE('01-07-$(vYear_Current)','DD-MM-YYYY') <= EFEC_END_DT
;
DROP TABLE DATA2_EXT;
left join (DATA2)
LOAD *
RESIDENT DATA1;
DROP TABLE DATA1;
SET vYear_Current = ;
next _i
;
