topic Re: Rowno per order in QlikView

Rowno per order

francisvandergr — Wed, 25 Nov 2020 16:16:04 GMT

I have included a script.

I like to have for each order a rowno() starting for every order with 1

Can someone help me with this script.

See the example

Re: Rowno per order

sunny_talwar — Tue, 26 Apr 2016 10:52:42 GMT

Try this:

ORDERS:

LOAD *,

AutoNumber(RowNo(), Orderno) as RowNo;

LOAD * INLINE [

Orderno, Article, Qty, Amount

100, 1, 1, 10

120, 2, 1, 11

130, 2, 2, 66

150, 3, 3, 33

100, 2, 1, 2

120, 4, 50, 100

150, 3, 40, 80

150, 3, 50, 70

];

Re: Rowno per order

swuehl — Tue, 26 Apr 2016 10:52:53 GMT

You can use autonumber() to get a rowno in the script:

ORDERS:

LOAD *, AutoNumber(recno(), Orderno) as rowno INLINE [

Orderno, Article, Qty, Amount

100, 1, 1, 10

120, 2, 1, 11

130, 2, 2, 66

150, 3, 3, 33

100, 2, 1, 2

120, 4, 50, 100

150, 3, 40, 80

150, 3, 50, 70

];

You can sort your input table differently if you need a special sort order for rowno.

edit:

For example like

ORDERS:

LOAD * INLINE [

Orderno, Article, Qty, Amount

100, 1, 1, 10

120, 2, 1, 11

130, 2, 2, 66

150, 3, 3, 33

100, 2, 1, 2

120, 4, 50, 100

150, 3, 40, 80

150, 3, 50, 70

];

LOAD *, AutoNumber(recno(), Orderno) as rowno

Resident ORDERS

ORDER BY Amount;

DROP TABLE ORDERS;

Re: Rowno per order

Kushal_Chawda — Tue, 26 Apr 2016 11:02:32 GMT

another way

Data:

LOAD *

FROM Table.qvd(qvd);

new:

noconcatenate

LOAD *,

if(rowno()=1 or Orderno<>previous(Orderno),1,peek('Rowno')+1) as Rowno

Resident Data

order by Orderno;

drop table Data;

Re: Rowno per order

sunny_talwar — Tue, 26 Apr 2016 11:40:27 GMT

I guess my solution was not as helpful as others

But I am glad you were able to resolve your issue

Best,

Sunny