Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QSense
Creator II
Creator II

row_number over partition by

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

Labels (2)
1 Solution

Accepted Solutions
joseph_morales
Creator II
Creator II

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

 

Best Regards,
Joseph Morales

View solution in original post

4 Replies
joseph_morales
Creator II
Creator II

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

 

Best Regards,
Joseph Morales
QSense
Creator II
Creator II
Author

Hi Joseph,

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

QSense
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
;