row_number over partition by

[QSense]

hello ,

I have inline table like 

LOAD * INLINE [
MatNo, OperationNo, OperationDate, Quantity
x, 1, 12/11/2018, 1
x, 2, 12/12/2018, 1
x, 3, 12/13/2018, 1

y, 3, 12/18/2018, 1

y, 3, 12/19/2018, 1
];

I would like to give row number for each row according to matno and operationdate.

The result would like 

MatNo, OperationNo, OperationDate, Quantity,Rownum
x, 1, 12/11/2018, 1,1
x, 2, 12/12/2018, 1,2
x, 3, 12/13/2018, 1,3

y, 3, 12/18/2018, 1

y, 3, 12/19/2018, 2.

Thanks in advance

[joseph_morales · Accepted solution]

Hi @QSense , 

Try with this:

LOAD *,
AutoNumber(RowNo(),MatNo) as rownum;
LOAD * INLINE [
MatNo, OperationNo, OperationDate, Quantity
x, 1, 12/11/2018, 1
x, 2, 12/12/2018, 1
x, 3, 12/13/2018, 1

y, 3, 12/18/2018, 1

y, 3, 12/19/2018, 1
];

Regards

[QSense]

Hi Joseph,

I have just seen your post. After I tried your solution offer, I will give feedback.

[QSense]

Thanks again. It gives a distinct number for each row group my matno but

I would like to order by operationdate.

It would like be for matno y.

MatNo, OperationNo, OperationDate, Quantity,ROWNUM
x, 1, 12/11/2018, 1,1
x, 2, 12/12/2018, 1,2
x, 3, 12/13/2018, 1,3

y, 3, 12/19/2018, 2

y, 3, 12/18/2018, 1

[QSense]

In the below,

I have added order by . it is ok for my case.

Thanks .

TABLE:
LOAD * INLINE [
MatNo, OperationNo, OperationDate, Quantity
x, 1, 12/11/2018, 1
x, 2, 12/12/2018, 1
x, 3, 12/13/2018, 1

y, 3, 12/18/2018, 1

y, 3, 12/19/2018, 1
z, 3, 12/17/2018, 1

z, 3, 12/16/2018, 1
z, 3, 12/13/2018, 1

k, 3, 12/10/2018, 1
k, 3, 12/08/2018, 1

k, 3, 12/05/2018, 1
];

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