topic Re: Unique row counter in QlikView

Unique row counter

semgem12 — Wed, 25 Nov 2020 16:16:04 GMT

Hello all

I have a data set as follows

Order No	Date	Dept
10000	01/05/16	A
10001	01/05/16	B
10002	01/05/16	B
10003	01/05/16	B
10004	01/05/16	C
10005	01/05/16	C
10006	01/05/16	D
10007	02/05/16	B

The data is being loaded and sorted by date and within date by dept. I would like to somehow assign a row number during the load script to each order line so that for each dept within a date, the row number starts at 1 and increments until the next dept at which point it starts at 1 again, i.e.

Order	Date	Dept	Row Number
10000	01/05/16	A	1
10001	01/05/16	B	1
10002	01/05/16	B	2
10003	01/05/16	B	3
10004	01/05/16	C	1
10005	01/05/16	C	2
10006	01/05/16	D	1
10007	02/05/16	B	1

Would be grateful if someone could point me in the right direction please. Thanks in advance.

Re: Unique row counter

antoniotiman — Tue, 20 Sep 2016 16:27:32 GMT

Hi,

try like this

LOAD [Order No],
Date,
Dept,
If(Peek(Dept)=Dept and Peek(Date)=Date,Peek(RowNo)+1,1) as RowNo
FROM
"https://community.qlik.com/message/1126571"
(html, codepage is 1252, embedded labels, table is @1);

Regards,

Antonio

Re: Unique row counter

semgem12 — Tue, 20 Sep 2016 16:46:47 GMT

Wow! Thank you so much, that has worked perfectly. It has generated the row numbers exactly as I needed them. Thank you for replying to my question so quickly.

Re: Unique row counter

sunny_talwar — Tue, 20 Sep 2016 16:47:33 GMT

You can also try this:

Table:

LOAD [Order No],

Date,

Dept,

AutoNumber(RowNo(), Date&Dept) as [Row Number]

FROM

[https://community.qlik.com/thread/233353]

(html, codepage is 1252, embedded labels, table is @1);

Re: Unique row counter

semgem12 — Tue, 20 Sep 2016 16:50:14 GMT

Thank you Sunny, that works perfectly too. Great to have two different solutions.

Re: Unique row counter

Digvijay_Singh — Tue, 20 Sep 2016 16:55:04 GMT

try this -

Temp:

LOAD [Order No],

Date,

Dept

FROM

[https://community.qlik.com/thread/233353]

(html, codepage is 1252, embedded labels, table is @1);

Final:

Load *,

If(Previous(Dept)=Dept and Previous(Date)=Date,Peek(RowNo)+1,1) as RowNo

resident Temp

Order By Date,Dept;

drop table Temp;