topic Re: Order by & Group by in QlikView

Order by & Group by

Mon, 06 Oct 2014 09:34:53 GMT

Hi All,

How can I use order by and group by together. Order by only works using a resident load. When I try to take the short cut

Load *

Resident XYZ

Order by [Received Date] asc;

And then another load statement to group, this doesnt work.

In the resident load, do I need to specify all fields and not use Load *?

Can I use Load * when I do the group by part?

Because this seems like a long repetition of the script.

Thanks

Re: Order by & Group by

Gysbert_Wassenaar — Mon, 06 Oct 2014 09:38:40 GMT

The order by needs to come after the group by:

LOAD

FieldA,

FieldB,

sum(FieldC) as SumC

GROUP BY FieldA, FieldB

ORDER BY FieldB desc;

Re: Order by & Group by

anbu1984 — Mon, 06 Oct 2014 09:40:24 GMT

You can use Group by and Order by in Resident load. All the columns mentioned in Load except aggregated columns should be included in Group by clause

Load Col1,Col2,Max(Col3)

Resident XYZ

Group by Col1,Col2

Order by Col1 desc,Col2 asc;

Re: Order by & Group by

deepakqlikview_123 — Mon, 06 Oct 2014 09:42:01 GMT

Resident load is works as view when u can select * it will take all columns from previously loaded table.

if u want to take some selected column u are able to select it.

if u do not want previous table or field simply drop it to avoid repetation.

Thanks

Re: Order by & Group by

Anonymous — Mon, 06 Oct 2014 09:42:36 GMT

Herbert,

If you want to group by and order by, you first have to group by, loading this field, and then with a new statement, ordering by it. Then, drop it this field you don't need it anymore.

Hope it helps.

Marc.

Re: Order by & Group by

Mon, 06 Oct 2014 09:53:53 GMT

Thanks Gysbert