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: 
Not applicable

Row numbering by two dimension

Hello all,

I have a problem when I put RowNo to the straight table, ordering by field changed. Idea to get in result the following:

#
A
B
Value
1AADD687
2BBDD566
3AASS345
4GGDD111

Thanks in advance!

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

You can try this:

Rank(Total Aggr(Sum(Value), A, B))

See the attached. However, if you reverse the sort of value, the count will reverse as well.

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

16 Replies
Not applicable
Author

To me it seems pretty strange!

I added rowno() in the loadscript and then it worked, but IMO it should work in the front-end as wel...

Is moving it to the loadscript workable for you?

load

RowNo() as customRowNo,* Inline

[A,B,Value

AA,SS,345

BB,DD,566

AA,DD,687
GG,DD,111]
;

And of course I'm interested in the explanation why it doesn't work in the front-end.
 

Not applicable
Author

Thanks Roberto,

When I added RowNo to the script, I have the following result.

customRowNoABValue
3AADD687
2BBDD566
1AASS345
4GGDD111

I need to keep ordering only by Value field and show row number in the first column, any idea how to implement it?

Not applicable
Author

kostak wrote:

I need to keep ordering only by Value field and show row number in the first column, any idea how to implement it?

I don't understand what is missing.

The result table orders by only the valuefield and shows the rownumber in the first column...

Can you explain what the expected result is?

Not applicable
Author

Incorrect:

customRowNoABValue
3AADD687
2BBDD566
1AASS345
4GGDD111

CustomRowNo field should always shows values as 1,2,3,4 (just row number position in the table)

Correct:

customRowNoABValue
1AADD687
2BBDD566
3AASS345
4GGDD111
Not applicable
Author

Here you are.

tmp:
load RowNo() as customRowNo,* Inline
[A,B,Value
AA,SS,345
BB,DD,566
AA,DD,687
GG,DD,111]

;


NoConcatenate

result:
load RowNo() as correctRowNo

,*
Resident tmp order by Value desc;

drop table tmp;

Use correctRowNo instead of customRowNo, then it works.

Still I don't understand why it doesn't work in the frond-end.

Anyone?

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

No need to load it.

Use following as an expression in the straight table (not dimension):

=Alt(Above(Column(1))+1, 1)

This will count the rows from 1. Change the index in Column() to the correct value for your table. This is the position of this expression in the table's expression list.

Once this is working, just drag the column to the left most position in the chart (left of the dimensions).

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Jonathan,
It works fine when only one dimension exist in straight table, but not working for two dimension.

Here is the result when I added Alt(Above(Column(2))+1, 1) to my test file:

A
BValueAlt(Above(Column(2))+1, 1)
AADD6871
BBDD5662
GGDD1113
AASS3451
jonathandienst
Partner - Champion III
Partner - Champion III

Add a TOTAL qualifier, that should do the trick:

=Alt(Above(TOTAL Column(1))+1, 1)

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

Actually, you can use

=RowNo(TOTAL)

to get the same effect! Which would be preferable to the Above trick.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein