Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Using both "Group By" and "Order By"

Using a table like the following:

LOAD FirstValue(name) INLINE [

    id, type, name, age

    1, man, dave, 65

    2, man, chris, 66

    3, woman, mary, 64

    4, girl, karen, 27

    5, woman, betty, 60

    6, boy, john, 22

    7, man, peter, 40

    8, boy, jay, 21

    9, girl, sandra, 20

    10, man, bob, 23

] Group by type Order by age;

To get a listbox like this:

  • bob
  • betty
  • sandra
  • jay

But the script dont allow me to use Group By and Order By at the same time, any ideas?

best regards.

6 Replies
Not applicable

Using both "Group By" and "Order By"

Sort the listbox itself by age. Go to the sort tab and put in an expression like avg(age) and tell it to sort ascending.

Not applicable

Using both "Group By" and "Order By"

Does that mean you can't combine the two commands (Order By and Group By) in the same statement? What you suggested is OK but not the same thing, whould like to solve it using the script dialog.

Thank you!

Not applicable

Using both "Group By" and "Order By"

I think the only place you can use them both is from a resident table. You could load the inline data & group it into a resident table then pull it out of there in the order you prefer. I would only do that if there is further analysis you need to do on the data based on that load order within the script.

You will still need to address how it sort's in the app itself & go back into the sort tab and force it to sort on load order. Otherwise it will default to numeric ascending / alpha ascending.

nagaiank
Valued Contributor III

Using both "Group By" and "Order By"

The following script works.

Tab1:

LOAD * INLINE [

    id, type, name, age

    1, man, dave, 65

    2, man, chris, 66

    3, woman, mary, 64

    4, girl, karen, 27

    5, woman, betty, 60

    6, boy, john, 22

    7, man, peter, 40

    8, boy, jay, 21

    9, girl, sandra, 20

    10, man, bob, 23

];

Tab2:

NoConcatenate LOAD * Resident Tab1 Order By type, age;

DROP Table Tab1;

Tab3:

LOAD FirstValue(name) as Firstname Resident Tab2 Group By type;

Not applicable

Using both "Group By" and "Order By"

If you use group by, it is not allowed (nor logical) to use any fields outside the group without aggregation. All records belonging to the same group are reduced to one row. The field age does not have a single value as different members to that group can have different ages. What you can do is:

order by Avg(age)

Not applicable

Re: Using both "Group By" and "Order By"

Plz check the attcahed app.

Hope it helps...

Community Browser