Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
souadouert
Contributor III

sorting in load

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

6 Replies

Re: sorting in load

Try this?

Table:

Load * from table ;

Ledt join

First 20

Load agency resident table order by score desc;

souadouert
Contributor III

Re: sorting in load

Hello Anil ,


the first 20 clients for each agency

MVP
MVP

Re: sorting in load

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
Contributor III

Re: sorting in load

Hello , Jonathan  , is not the solution

souadouert
Contributor III

Re: sorting in load

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
Contributor III

Re: sorting in load

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

Community Browser