Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
souadouert
Specialist
Specialist

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
Anil_Babu_Samineni

Try this?

Table:

Load * from table ;

Ledt join

First 20

Load agency resident table order by score desc;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
souadouert
Specialist
Specialist
Author

Hello Anil ,


the first 20 clients for each agency

jonathandienst
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
souadouert
Specialist
Specialist
Author

Hello , Jonathan  , is not the solution

souadouert
Specialist
Specialist
Author

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

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