Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
 
					
				
		
 souadouert
		
			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
Try this?
Table:
Load * from table ;
Ledt join
First 20
Load agency resident table order by score desc;
 
					
				
		
 souadouert
		
			souadouert
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Hello Anil ,
the first 20 clients for each agency
 
					
				
		
 jonathandienst
		
			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 
Order By agence, score desc;
 
					
				
		
 souadouert
		
			souadouert
		
		
		
		
		
		
		
		
	
			
		
		
			
					
		Hello , Jonathan  , is not the solution 
 
					
				
		
 souadouert
		
			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
		
			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
