topic Re: sorting in load in QlikView

sorting in load

souadouert — Thu, 18 Jan 2018 08:49:19 GMT

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

Re: sorting in load

Anil_Babu_Samineni — Thu, 18 Jan 2018 08:53:13 GMT

Try this?

Table:

Load * from table ;

Ledt join

First 20

Load agency resident table order by score desc;

Re: sorting in load

souadouert — Thu, 18 Jan 2018 08:59:13 GMT

Hello Anil ,

the first 20 clients for each agency

Re: sorting in load

jonathandienst — Thu, 18 Jan 2018 09:00:39 GMT

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;

Re: sorting in load

souadouert — Thu, 18 Jan 2018 09:26:19 GMT

Hello , Jonathan , is not the solution

Re: sorting in load

souadouert — Thu, 18 Jan 2018 09:33:30 GMT

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;

Re: sorting in load

kenphamvn — Thu, 18 Jan 2018 09:49:33 GMT

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