Order By, Peek, Rank in script

Fri, 16 May 2014 11:18:58 GMT

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,

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,

ACC_ID,

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,

ACC_ID,

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?

Re: Order By, Peek, Rank in script

Fri, 16 May 2014 13:37:44 GMT

Closing this link.

It's working fine..

topic Re: Order By, Peek, Rank in script in QlikView

Order By, Peek, Rank in script

Re: Order By, Peek, Rank in script