topic Re: Row numbering by two dimension in QlikView

Row numbering by two dimension

Tue, 10 Jul 2012 08:49:04 GMT

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
1	AA	DD	687
2	BB	DD	566
3	AA	SS	345
4	GG	DD	111

Thanks in advance!

Re: Row numbering by two dimension

Tue, 10 Jul 2012 08:58:08 GMT

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.

Re: Row numbering by two dimension

Tue, 10 Jul 2012 09:16:02 GMT

Thanks Roberto,

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

customRowNo	A	B	Value
3	AA	DD	687
2	BB	DD	566
1	AA	SS	345
4	GG	DD	111

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

Re: Row numbering by two dimension

Tue, 10 Jul 2012 09:21:07 GMT

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?

Re: Row numbering by two dimension

Tue, 10 Jul 2012 09:28:29 GMT

Incorrect:

customRowNo	A	B	Value
3	AA	DD	687
2	BB	DD	566
1	AA	SS	345
4	GG	DD	111

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

Correct:

customRowNo	A	B	Value
1	AA	DD	687
2	BB	DD	566
3	AA	SS	345
4	GG	DD	111

Re: Row numbering by two dimension

Tue, 10 Jul 2012 09:34:07 GMT

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?

Re: Row numbering by two dimension

jonathandienst — Tue, 10 Jul 2012 10:12:43 GMT

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

Re: Row numbering by two dimension

Tue, 10 Jul 2012 10:28:02 GMT

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	B	Value	Alt(Above(Column(2))+1, 1)
AA	DD	687	1
BB	DD	566	2
GG	DD	111	3
AA	SS	345	1

Re: Row numbering by two dimension

jonathandienst — Tue, 10 Jul 2012 10:35:23 GMT

Add a TOTAL qualifier, that should do the trick:

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

Regards

Jonathan

Re: Row numbering by two dimension

jonathandienst — Tue, 10 Jul 2012 10:38:28 GMT

Actually, you can use

=RowNo(TOTAL)

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

Regards

Jonathan

Re: Row numbering by two dimension

Tue, 10 Jul 2012 10:42:54 GMT

The result now:

A	B	Value	Alt(Above(Column(2))+1, 1)
AA	SS	345	1
BB	DD	687	2
BB	DD	566	3
GG	DD	111	4

And it is not correct, Value field should be ordered as descend.

Correct result shoud be as

customRowNo	A	B	Value
1	AA	DD	687
2	BB	DD	566
3	AA	SS	345
4	GG	DD	111

Re: Row numbering by two dimension

jonathandienst — Tue, 10 Jul 2012 11:08:21 GMT

Then sort the table by Value and drag the row no to the left.

Jonathan

Re: Row numbering by two dimension

Tue, 10 Jul 2012 11:16:38 GMT

Sorry, but it doesn't work.

Re: Row numbering by two dimension

jonathandienst — Tue, 10 Jul 2012 13:59:21 GMT

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

Re: Row numbering by two dimension

Tue, 10 Jul 2012 14:23:26 GMT

Thanks a lot Jonathan, it works perfectly!!!

Re: Row numbering by two dimension

Tue, 10 Dec 2013 11:45:58 GMT

Hi JOnathan,

Thanks for this.

Can you please let me know how can I get Rank in Reverse Order for the descending Sorted values?

Thanks in Advance

Regards,

Susvith

Re: Row numbering by two dimension

Tue, 10 Dec 2013 12:09:57 GMT

something like that:

Rank(Total Aggr(Sum(Value)*-1, A, B))