Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
joeybird
Contributor III

restart autonumber qlik sense


Hi

each CustomerID  has number of OrderID

This order id can look like this x11533322

I wish to autonumber these, but restart the autonumber per customer

I have tried to use in the load

AutoNumber( CustomerID & '|' & OrderID ) as ID

AutoNumber( OrderID ) as ID

I wish for the output to show as

CustomerID - ID

A                     1

A                     2

A                     3

A                     4

B                     1

B                     2

B                     3

Please help

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: restart autonumber qlik sense

Hi Joanna,

maybe

Autonumber(RowNo(),CustomerID) as ID

Regards,

Antonio

View solution in original post

4 Replies
antoniotiman
Honored Contributor III

Re: restart autonumber qlik sense

Hi Joanna,

maybe

Autonumber(RowNo(),CustomerID) as ID

Regards,

Antonio

View solution in original post

Re: restart autonumber qlik sense

Or even this:

AutoNumber(RecNo(), CustomerID) as ID

While I have heard that AutoNumber is sometime slow for huge databases, so you can also use Peek/Previous

If(CustomerID = Previous(CustomerID), RangeSum(Peek('ID'), 1), 1) as ID

But make sure to order your data if it is not already ordered by CustomerID

joeybird
Contributor III

Re: restart autonumber qlik sense

Hi

thanks both

Autonumber(RowNo(),CustomerID) as ID

worked x

monajrathi
New Contributor

Re: restart autonumber qlik sense

Hi,

I have similar issue but I need to autonumber on multiple columns, but the autonumber gives same output number if the value exists in other column.

In below, if you see for value "200" the autonumber is 3 and it is assigned wherever I have 200 across the columns. Let me know if I can use any other function or how I can reset the autonumber counter back to 1.

ID  a1  a1_Autonum n1  ni_autonum

1   100  1         200  3

2   200  3         150  9

base:

load * Inline [

id, a1, n1, r1

1,100, 200, 300

2,200, 150, 240

3,250, 101, 750

4,600, 150, 225

5,1000, 845, 990

6,101, 1234, 8954

7,200, 345, 675

8,600, 750, 101

9,550, 350, 605

10,500, 185, 230

];

let c = NoOfRows('base');

test1:

load *,AutoNumber(a1) as a1rnk, $(c) as cnt

resident base

order by a1;

test:

load *,'' as samplecolumn resident test1;

drop table test1;

test2:

load AutoNumber(n1) as n1rnk, id, 'test2' as test2

resident base

order by n1;

left join (test)

load n1rnk, id resident test2;

drop table test2;

drop table base;

exit script;