SQL ROW_NUMBER() OVER(PARTITION BY ORDER BY 'Row Number' in Qlik Sense

[cfountain72]

Hello, 

I am trying to do something that seems very simple, but I can't get the syntax quite right. I have Customers and Order Numbers. I would like to add a RowNumber that restarts at each Customer, sorting by Order Number. For those with SQL experience, this would be something like ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Order Number) as RowNumber. I've seen an answer elsewhere, but it directs me to a qvw file, and I don't have QlikView installed to view it.

Below are what the results should look like:

Customer	Order Number	RowNumber
AAA	1	1
AAA	2	2
BBB	3	1
BBB	4	2
BBB	5	3
BBB	6	4
CCC	7	1
CCC	8	2
CCC	9	3
DDD	10	1

I've tried Autonumber() and Peek, but nothing has worked quite as expected. 

Thanks in advance for any help,

Chris

[rwunderlich · Accepted solution]

In Qlik Sense script, this would be:

AutoNumber(OrderNumber, Customer) as RowNumber

with an "Order By OrderNumber" clause  in the load.

-Rob

[TcnCunha_M]

Hello

Try this

TABLE:
Load *,
Customer As Sequencer;
LOAD * INLINE [
Customer, Order Number, RowNumber
AAA, 1, 1
AAA, 2, 2
BBB, 3, 1
BBB, 4, 2
BBB, 5, 3
BBB, 6, 4
CCC, 7, 1
CCC, 8, 2
CCC, 9, 3
DDD, 10, 1
];

Qualify *;
ROWNUM:
LOAD *,
AutoNumber(RowNo(),Sequencer) as rownum
Resident TABLE
order by Sequencer

[Aasir]

LOAD
Customer,
[Order Number],
AutoNumber([Order Number], Customer) as RowNumber
FROM YourDataSource;

[cfountain72]

Thanks Rob,

Happy Thanksgiving!

[cfountain72]

Thanks Rob,

I also tried this, which seems to work in my few tests. Is there an advantage of one vs the other approach?

Load *
,AutoNumber(RowNo(), Customer) as OrderSeq
Resident Table_Test;

Or might it be working only if the data was already in the necessary order?

[rwunderlich]

If you have only one row per OrderNumber, both forms will return the same result and will likely perform exactly the same. 

The difference will come in to play if you had multiple input rows per OrderNumber, for example if you had Order Lines or update records.  In that case RecNo() will return a unique seq for each line, whereas using OrderNumber would ensure that the same seq would be assigned per OrderNumber. 

So it depends on what you're after.  I prefer to use the explicit "grouping key" like OrderNumber when available. I think it makes it more explicit what I'm doing and avoids surprises. I use RecNo() when I don't have a key or truly want a unique number per record. 

Note that the new Window() function is also now an option and it allows for specifying a sort order in the function (as opposed to order by in the Resident).  The downside of the Window() form is that it would only work with RecNo(). I don't see how you could use it with OrderNumber.

Window(RecNo(), Customer) as OrderSeq

Window(RecNo(), Customer, 'ASC', OrderDate) as OrderSeq

https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/Scripting/WindowFunc...

-Rob

[cfountain72]

Thanks for extra the info Rob. Yes, in this case, each Order Number unique to a row.