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

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

ihuynhi — Thu, 03 Nov 2022 13:51:11 GMT

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.

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

MarcoWedel — Sat, 21 May 2022 19:58:32 GMT

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

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

ihuynhi — Mon, 23 May 2022 08:30:41 GMT

Hi,

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

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

MarcoWedel — Mon, 23 May 2022 20:17:05 GMT

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;