topic Re: Number table row consecutively in QlikView

Number table row consecutively

Mon, 04 Nov 2013 13:45:59 GMT

Hi!

We would like to add a column that simply shows "1" for the first row, "2" for the 2nd row and so on.

It should always show the same numbering even when the sorting is changed.

Thanks for your help!

Best Regards

Sebastian

Re: Number table row consecutively

Clever_Anjos — Mon, 04 Nov 2013 13:48:52 GMT

Try rowno() as expression

Re: Number table row consecutively

swuehl — Mon, 04 Nov 2013 13:49:47 GMT

=rowno(total)

when using multiple dimensions.

Re: Number table row consecutively

Mon, 04 Nov 2013 15:48:30 GMT

Hi,

unfortunatly rowno() breaks sorting completely.

Regards

Sebastian

Re: Number table row consecutively

Mon, 04 Nov 2013 15:54:11 GMT

This one works!

Re: Number table row consecutively

Mon, 04 Nov 2013 16:26:46 GMT

No, it does not work. Sorting is not possible anymore, sort settings are completely ignored, when you have a column with a formula "rowno(total)". The table itself is fine though.

Re: Number table row consecutively

Mon, 04 Nov 2013 16:30:29 GMT

Are you using rowno() in an expression? If yes then put it in the LOAD statement of the table in the QlikView script instead,

e.g.

LOAD

Rowno() as UID,

Field1,

Field2,

...

FROM ....

Re: Number table row consecutively

Mon, 04 Nov 2013 16:44:32 GMT

Yes, I tried to put it as an expression. Putting it into the script does not work either, the value for the row number is empty after (reloading the script and) adding it to my table.

Re: Number table row consecutively

Mon, 04 Nov 2013 17:55:53 GMT

Sebastian Kather wrote:

...the value for the row number is empty after (reloading the script and) adding it to my table.

It should work. Try RecNo() instead of RowNo() (in the script)?

From the help guide:

Counter Functions

These functions do not have any parameters. The parentheses are however still required.

RecNo( )

Returns an integer for the number of the currently read row of the internal table. The first record is number 1.

RowNo( )

Returns an integer for the position of the current row in the resulting QlikView internal table. In contrast to RecNo( ), which counts the records in the raw data table, the RowNo( ) function does not count records that are excluded by where clauses and is not reset when a raw data table is Concatenation to another. The first row is number 1.

Examples:

Raw data tables:

Tab1.csv
A	B
1	aa
2	cc
3	ee

Tab2.csv
A	B
5	xx
4	yy
6	zz

QVTab:

Load *, RecNo( ), RowNo( ) from Tab1.csv where A<>2;

Load *, RecNo( ), RowNo( ) from Tab2.csv where A<>5;

The resulting QlikView internal table:

QVTab
A	B	RecNo( )	RowNo( )
1	aa	1	1
3	ee	3	2
4	yy	2	3
6	zz	3	4

Re: Number table row consecutively

Gysbert_Wassenaar — Mon, 04 Nov 2013 18:07:38 GMT

Show us. Post a document that demonstrates the issue. I cannot replicate the behaviour you're describing.

Re: Number table row consecutively

Clever_Anjos — Mon, 04 Nov 2013 18:55:28 GMT

Would you mind sharing a qvw?

I´ve done a small test, seems to sort ok. Maybe I´m not understanding your point of view

Re: Re: Number table row consecutively

Mon, 04 Nov 2013 19:30:18 GMT

Gysbert Wassenaar

Clever Anjos

Thank you all for the overwhelming support. I did not expect such a lively community!

Attached two files showing the problem (xls and qvw).

As you can see the table cannot be sorted by "AVG(rate_overall)".

If you remove the column "RowNo(TOTAL)" it works fine.

Putting RecNo or RowNo into the script did not seem to work either, due to the aggregate function used in the table.

My Task is to sort the table by "AVG(rate_overall)" or other columns and show one column containing the row number.

I would like to make a ranking of location ratings and the ranking should show, too.

Looking forward to your ideas!

Re: Number table row consecutively

Mon, 04 Nov 2013 20:01:25 GMT

Hi Sebastian,

Does the attached document illustrate my understanding to your question correctly?

I guess RowNo() as UID (that Kai Hilton-Jones

suggested) in the script shall help you.

Let us know how it goes.

Best wishes,

Re: Re: Number table row consecutively

Mon, 04 Nov 2013 20:12:21 GMT

Hi Kadir,

I am testing the personal edition, cannot open your file - sorry!

Attached my try to include rowNo in the script, which apparently does not work (see table).

Best Regards

Sebastian

Re: Number table row consecutively

Clever_Anjos — Mon, 04 Nov 2013 20:29:56 GMT

Checked here, you´re right.

I don´t know why is happening this way

Re: Re: Re: Number table row consecutively

swuehl — Mon, 04 Nov 2013 22:48:43 GMT

This does not work because you have multiple records per a given dimension value, so QV does not know what to return for UID.

But anyway, it seems that wouldn't solve your problem, because you don't want a rowno determined during script load, but determined from chart y-values (I assume also you might want to dynamically filter the table).

If you just want to sort your avg value, then show the rank, I would suggest using the rank() function in your chart expression:

=num(rank(-Avg(rate_overall),4))

for average sorted asc, and without the minus sign for desc order.

Or if you want a fixed rowno, use

=rowno(total)

as expression (chart inter records limit the options QV allow to sort, check the Help for more detail) and use a sort expression like =avg(rate_overall) for the dimension.

I hope this helps,

Stefan

Re: Re: Re: Number table row consecutively

Tue, 05 Nov 2013 07:50:55 GMT

Hi!

"=num(rank(-Avg(rate_overall),4))" works fine but is limited to be tied to the rate_overall column.

So this should be considered as a workaround but not a solution.

Anyway this is the best answer so far!

Regards

Sebastian