Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
ihuynhi
Contributor II
Contributor II

Order By doesn't work in the right way and how to add row numbers per group in Qlik View?

I have a dataset with a format (notice the Q is actually month here, the column name is wrong)

g1.PNG

that I want to group and sort so that the result will look like this:

q2.PNG

I have tried this so far

Data:
LOAD
"Year",
Q,
"Order",
Price
FROM [path]
(ooxml, embedded labels, table is Sheet1);

data2:

Load
"Year",
Q,
"Order",
Sum(Price) as sum_price
Resident Data
Group By "Year", Q, "Order"
Order By "Year", Q, "Order";

However, this is what I get

q3.PNG

I also tried to sort with just order and it doesn't work. I would also like to add row numbers as shown in the desired result table, which is based on the year, month, and order.

 

 

Labels (3)
2 Solutions

Accepted Solutions
MarcoWedel

If you want to have the result sorted by Order, Year and Q you need to actually code it like this.

One example including the row number as well:

data2:
Load Year,
     Q,
     Order,
     Sum(Price) as sum_price,
     AutoNumber(Q, Year&Order) as row_num
Resident Data
Group By Year, Q, Order
Order By Order, Year, Q;

 

hope this helps

Marco

View solution in original post

MarcoWedel

another version without AutoNumber():

 

data2:
LOAD *,
     If(Year&Order=Previous(Year&Order),Peek(row_num)+1,1) as row_num;
LOAD Year,
     Q,
     Order,
     Sum(Price) as sum_price
Resident Data
Group By Year, Q, Order
Order By Order, Year, Q;

 

View solution in original post

4 Replies
MarcoWedel

If you want to have the result sorted by Order, Year and Q you need to actually code it like this.

One example including the row number as well:

data2:
Load Year,
     Q,
     Order,
     Sum(Price) as sum_price,
     AutoNumber(Q, Year&Order) as row_num
Resident Data
Group By Year, Q, Order
Order By Order, Year, Q;

 

hope this helps

Marco

Hoffman14
Contributor
Contributor

Normally you can use ROW_NUMBER() as in the example below along with ORDER BY. If you try to use the ROW_NUMBER() function without ORDER BY clause, you will get an error as seen below. The function 'ROW_NUMBER' must have an OVER clause with ORDER BY  PointClickCare CNA

ihuynhi
Contributor II
Contributor II
Author

Hi,

Thank you for the answer, it worked! However, the AutoNumber is incredibly slow, is there any other way to add the row numbers?

MarcoWedel

another version without AutoNumber():

 

data2:
LOAD *,
     If(Year&Order=Previous(Year&Order),Peek(row_num)+1,1) as row_num;
LOAD Year,
     Q,
     Order,
     Sum(Price) as sum_price
Resident Data
Group By Year, Q, Order
Order By Order, Year, Q;