Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Order By, Peek, Rank in script

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?

1 Reply
Not applicable

Re: Order By, Peek, Rank in script

Closing this link.

It's working fine..

Community Browser