Unique row counter

[semgem12]

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.

[antoniotiman · Accepted solution]

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

[semgem12]

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.

[sunny_talwar]

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);

[semgem12]

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

[Digvijay_Singh]

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;