Hi All,
I have a requirement to fetch the First two record for the CUS_ID by the Modified_Date
Table1:
Sql Sel
RULE_ID,
CUS_ID,
PRODUCT,
TERR,
MODIFIED_DT,
F,
G,
ACC_ID,
H FROM CP_TBL ORDER BY RULE_ID,CUS_ID,PRODUCT,TERR,MODIFIED_DT;
Then in Transformation Load created a Composite key as MNL_ID and applied the peek logic with Rank
Temp_TBL1:
Load
RULE_ID&'_'&CUS_ID&'_' &PRODUCT&'_'&TERR&'_'&MODIFIED_DT as MNL_ID,
Date#(MODIFIED_DATE,'DD/MM/YYYY') as [Modified Date],
RULE_ID,
CUS_ID,
PRODUCT,
TERR,
MODIFIED_DT,
F,
G,
ACC_ID,
H
from [..Folder\CP.qvd](qvd);
Noconcatenate
Temp_TBL2:
Load
RULE_ID&'_'&CUS_ID&'_' &PRODUCT&'_'&TERR&'_'&MODIFIED_DT as MNL_ID,
Date#(MODIFIED_DATE,'DD/MM/YYYY') as [Modified Date],
RULE_ID,
CUS_ID,
PRODUCT,
TERR,
MODIFIED_DT,
F,
G,
ACC_ID,
H
from [..Folder\CP.qvd](qvd);
NoConcatenate
OVER_ID:
First 2 LOAD*,
If(MNL_ID=Peek(MNL_ID),Peek(Rank),RecNo()) as Rank
Resident temp2_OVERRIDE Order By %MNL_OVERRIDE_ID desc;
Concatenate
First 2 LOAD*,
If(MNL_ID=Peek(MNL_ID),Peek(Rank),RecNo()) as Rank
Resident temp1_OVR_ID Order By MNL_ID desc;
Is there anything wrong?
