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

[ihuynhi]

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

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

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

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.

[MarcoWedel · Accepted solution]

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]

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]

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 · Accepted solution]

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;