topic Re: row_number over partition by in QlikView

row_number over partition by

QSense — Sat, 16 Nov 2024 04:21:39 GMT

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

Re: row_number over partition by

joseph_morales — Sun, 24 Feb 2019 21:27:43 GMT

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

Re: row_number over partition by

QSense — Mon, 25 Feb 2019 05:35:44 GMT

Hi Joseph,

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

Re: row_number over partition by

QSense — Mon, 25 Feb 2019 05:44:48 GMT

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

Re: row_number over partition by

QSense — Mon, 25 Feb 2019 06:22:11 GMT

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
;