1 Reply Latest reply: May 16, 2014 9:37 AM by Gyanaranjan Tripathy RSS

    Order By, Peek, Rank in script

    Gyanaranjan Tripathy

      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?