Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor

## Row_number over partition by

hello ,

I have inline table like

LOAD * INLINE [
x, 201112, a1
x, 201212, a2
x, 201312, a2
x, 201412, a3
y, 201112, a3
y, 201212, a2
y, 201312, a2
y, 201412, a3
z, 201112, a4
z, 201212, a4
z, 201312, a3
z, 201412, a3

];

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

The result would like

id, date, code, rownum
x, 201112, a1, 1
x, 201212, a2, 2
x, 201312, a2, 2
x, 201412, a3, 3
y, 201112, a3, 1
y, 201212, a2, 2
y, 201312, a2, 2
y, 201412, a3, 3
z, 201112, a4, 1
z, 201212, a4, 1
z, 201312, a3, 2
z, 201412, a3, 2

autonumber() too slow TT

Labels (1)
• ### General Question

2 Solutions

Accepted Solutions
Partner - Creator

Try this

``````T1:
LOAD * INLINE [
A,B,C
x, 201112, a1
x, 201212, a2
x, 201312, a2
x, 201412, a3
y, 201112, a3
y, 201212, a2
y, 201312, a2
y, 201412, a3
z, 201112, a4
z, 201212, a4
z, 201312, a3
z, 201412, a3
];

noconcatenate

T2:
*
Resident T1
order by A,B,C asc
;

T3:
if(A<>previous(A),1,
if(A=previous(A) and C<>previous(C),peek('rownumber')+1,
if(A=previous(A) and C=previous(C),peek('rownumber')))) as rownumber,
*
Resident T2;

Drop Tables T1,T2;``````

Partner - Creator

Try this

``````T1:
LOAD * INLINE [
A,B,C
x, 201112, a1
x, 201212, a2
x, 201312, a2
x, 201412, a3
y, 201112, a3
y, 201212, a2
y, 201312, a2
y, 201412, a3
z, 201112, a4
z, 201212, a4
z, 201312, a3
z, 201412, a3
];

noconcatenate

T2:
*
Resident T1
order by A,B,C asc
;

T3:
if(A<>previous(A),1,
if(A=previous(A) and C<>previous(C),peek('rownumber')+1,
if(A=previous(A) and C=previous(C),peek('rownumber')))) as rownumber,
*
Resident T2;

noconcatenate

T4:
A,
B,
C,
IF(A=previous(A) and rownumber=previous(rownumber),null(),rownumber) as rownumber
Resident T3;

Drop Tables T1,T2,T3;``````
3 Replies
Partner - Creator

Try this

``````T1:
LOAD * INLINE [
A,B,C
x, 201112, a1
x, 201212, a2
x, 201312, a2
x, 201412, a3
y, 201112, a3
y, 201212, a2
y, 201312, a2
y, 201412, a3
z, 201112, a4
z, 201212, a4
z, 201312, a3
z, 201412, a3
];

noconcatenate

T2:
*
Resident T1
order by A,B,C asc
;

T3:
if(A<>previous(A),1,
if(A=previous(A) and C<>previous(C),peek('rownumber')+1,
if(A=previous(A) and C=previous(C),peek('rownumber')))) as rownumber,
*
Resident T2;

Drop Tables T1,T2;``````

Contributor
Author

Thank you.
Can I ask you more questions?

How about the following results?

id, date, code, rownum
x, 201112, a1, 1
x, 201212, a2, 2
x, 201312, a2, -
x, 201412, a3, 3
y, 201112, a3, 1
y, 201212, a2, 2
y, 201312, a2, -
y, 201412, a3, 3
z, 201112, a4, 1
z, 201212, a4, -
z, 201312, a3, 2
z, 201412, a3, -

Partner - Creator

Try this

``````T1:
LOAD * INLINE [
A,B,C
x, 201112, a1
x, 201212, a2
x, 201312, a2
x, 201412, a3
y, 201112, a3
y, 201212, a2
y, 201312, a2
y, 201412, a3
z, 201112, a4
z, 201212, a4
z, 201312, a3
z, 201412, a3
];

noconcatenate

T2:
*
Resident T1
order by A,B,C asc
;

T3:
if(A<>previous(A),1,
if(A=previous(A) and C<>previous(C),peek('rownumber')+1,
if(A=previous(A) and C=previous(C),peek('rownumber')))) as rownumber,
*
Resident T2;

noconcatenate

T4: