order by a calculated field

[#1]

Hi

I want to show top 100 clients (by sum of reservations)

how can i do this? I tried to use preceding load but it's not working...

first 100 load *,

TopAgencyBookings

order by TopAgencyBookings desc

;

LOAD

     AgencyID,

     count(if(RsvDate<=today()-1 and today()-30<=RsvDate,ResID)) as TopAgencyBookings

FROM ....

where  AgencyID <> 0

Group by AgencyID ;

[vishsaggi]

What is that not working are you not getting any data? Is your RsvDate format is same as Today() date format?

[gajapathy74]

If you are getting an error, it might be because you are using load * and also the calculated field which is already part of the *.

first 100 load *,

TopAgencyBookings

order by TopAgencyBookings desc

[#4]

this is the error msg I get:

Syntax error, missing/misplaced FROM:

Top_Agencies:

first 200 load AgencyID,

TopAgencyBookings

order by TopAgencyBookings desc

Top_Agencies:

first 200 load AgencyID,

TopAgencyBookings

order by TopAgencyBookings desc

[#5]

Hi Gajapathy,

I changed the first query to

Top_Agencies:

first 100 load AgencyID,

TopAgencyBookings

order by TopAgencyBookings desc

;

but still I received an error msg..

[tresesco · Accepted solution]

Order By would only work with Resident Load. Try like:

Temp:

first 100 load

          AgencyID,

          count(if(RsvDate<=today()-1 and today()-30<=RsvDate,ResID)) as TopAgencyBookings

From <>;

NoConcatenate

Final:

load AgencyID,

TopAgencyBookings

Resident Temp order by TopAgencyBookings desc ;

Drop table Temp;

[#7]

thank you for the help.

I tried to use this query but I receive an error msg :

Invalid expression

Top_Agencies_1:

first 100 load

          AgencyID,

          count(if(RsvDate<=today()-1 and today()-30<=RsvDate,ResID)) as TopAgencyBookings

from ... (qvd)

where AgencyID <> 0

[tresesco]

You are using count() - an aggregation function in the script, so you have to use Group By clause. May be something like:

Top_Agencies_1:

first 100 load

          AgencyID,

          count(if(RsvDate<=today()-1 and today()-30<=RsvDate,ResID)) as TopAgencyBookings

from ... (qvd)

where AgencyID <> 0 group by AgencyID;

[#9]

thank you