topic Re: order by a calculated field in QlikView

order by a calculated field

Wed, 25 Nov 2020 16:16:04 GMT

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 ;

Re: order by a calculated field

vishsaggi — Tue, 01 Nov 2016 16:37:32 GMT

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

Re: order by a calculated field

gajapathy74 — Tue, 01 Nov 2016 17:09:44 GMT

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

Re: order by a calculated field

Wed, 02 Nov 2016 08:12:27 GMT

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

Re: order by a calculated field

Wed, 02 Nov 2016 08:14:08 GMT

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..

Re: order by a calculated field

tresB — Wed, 02 Nov 2016 08:40:50 GMT

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;

Re: order by a calculated field

Wed, 02 Nov 2016 12:15:35 GMT

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

Re: order by a calculated field

tresB — Wed, 02 Nov 2016 12:21:09 GMT

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;

Re: order by a calculated field

Thu, 03 Nov 2016 11:16:59 GMT

thank you