sorting in load

[souadouert]

Hello,

I have a qvd file with a customer list with score and agencies,

ID_CLIENT|agence|name|score

I want to sort the customer by agency according to the score and only take the first 20 for each agency

LOAD %ID_CLIENT,

    ID_Client

agence

score

FROM

(qvd)

group by agence

order by score desc

but i have error script

[Anil_Babu_Samineni]

Try this?

Table:

Load * from table ;

Ledt join

First 20

Load agency resident table order by score desc;

[souadouert]

Hello Anil ,

the first 20 clients for each agency

[jonathandienst]

Perhaps this:

LOAD %ID_CLIENT,

    ID_Client,

    agence,

    score

Where Count <= 20;

LOAD %ID_CLIENT,

    ID_Client,

    agence,

    score,

    If(agence <> Previous(agence), 1, Peek('Count') + 1) as Count

FROM (qvd)

Order By agence, score desc;

[souadouert]

Hello , Jonathan  , is not the solution

[souadouert]

agence:

LOAD distinct

     %ID_AGENCE_CTOS_CLIENT as agenc

FROM

(qvd);

for vRow = 1 to NoOfRows('agence')

Let MyVar = Peek('agenc',vRow-1,'agence');

first 20

HGFINAL:

load *

FROM

(qvd)

Where %ID_AGENCE_CTOS_CLIENT = '$(MyVar)'

order by score;

Next

[kenphamvn]

Hi

try this

Source_Table:

Load * from table ;

Load * 
,autonumber(ID_Client,agence) as New_Key

resident Source_Table 
where autonumber(ID_Client,agence) <=20

order by score desc;

Regards,

An Pham