Utilisation of Rowno function with subgroups of records in a pivot table

[#1]

Hi all,

I'm facing difficulties limiting the output of this function to subgroups of records obtained by aggregation.

I try to be clearer with this example: a pivot table with three columns, COMPANY_ID, CUSTOMER_ID, PRIORITY

Putting PRIORITY column between COMPANY_ID column and CUSTOMER_ID I can order customers of each company with priority descending (or ascending, no matter).

Now I want to assign a row number to each row of a customer belonging the same company, independently from the row numbers of the customers belonging other companies.

Any suggestions? I need to find a solution asap...

Thanks a lot!

COMPANY PRIORITY CUSTOMER ROWNO

AAAA 1 Mr. X 1

AAAA 2 Mr. Y 2

AAAA 2 Mr. Z 3

AAAA 3 Mr. W 4

BBBB 1 Mr. Q 1

BBBB 3 Mr. J 2

BBBB 4 Mr. S 3

CCCC 2 Mr.F 1

CCCC 4 Mr.G 2

CCCC 5 Mr.T 3

[johnw]

This seems to work:

aggr(rowno(),COMPANY,CUSTOMER)

See attached.

[#3]

Thank you very much John!

I'm training with expressions..

A question: is there some more accurated documentation on expressions and set analysis, different from the official one?

[johnw]

alecatta wrote:is there some more accurated documentation on expressions and set analysis, different from the official one?

I am not aware of any unofficial documentation that is more accurate than the official documentation.

[#5]

Hi,

Just in addition to this topic. Is it possible to have a row number column when one of the dimensions is pivoted as horizontal along the top? The solution here doesn;t seem to work when this is the case?

Thanks,

[johnw]

I don't know what you're asking for, but the attached file shows three guesses, the expressions for which are below.

rowno()
rowno(total)
aggr(nodistinct rowno(),COMPANY)

[#7]

But if added rowno function as the expression, the other expression, cannot the sort. How to solve this problem.