RowNo() Help

[#1]

Hi,

As an example, here is a base table

Name, Category

James, Top

Ben, Top

Harry, Med

Sophie, Med

Sally, Low

How would I create a straight table, which looks like the below:

Name, Category, expr1

James, Top, 1

Ben, Top, 1

Harry, Med, 2

Sophie, Med, 2

Sally, Low, 3

To summarise, I need to build an expression which "autonumbers" on the dimension "Category"

How could I do this?

[#2]

You can create an additonal field in the data model in order to rank your categories:

Name, Category, Rank

James, Top, 1

Ben, Top, 1

Harry, Med, 2

etc..

Other aproximation could be declaring Category field as dual.

Regards.

[#3]

Thanks for your response,

unfortunately- I can't make any changes to the data model. This must all be done inside an expression.

[#4]

Hi Wallerjc,

Here you have a sample solution only in front end.. Still, solving this issue in Script will be recomended..

[#5]

You can use either mixmatch() or wildmatch() functions

sth like : mixmatch(Category, 'Top', 'Mid', 'Low')

Fabrice

[#6]

hi

in script write this

pick(match(Category,'Top','Med','Low'),'1','2','3')  as newfield

[#7]

hi

try this

load *,

pick(match(Category,'Top','Med','Low'),'1','2','3')  as newfield

Inline [

Name, Category

James, Top

Ben, Top

Harry, Med

Sophie, Med

Sally, Low

] ;

then output like this

Name	Category	newfield
James	Top	1
Ben	Top	1
Harry	Med	2
Sophie	Med	2
Sally	Low	3

[#8]

hi

also try this

load *,

if(Previous(Category)=Category,peek(field),rangesum(1,peek(field))) as field

Inline [

Name, Category

James, Top

Ben, Top

Harry, Med

Sophie, Med

Sally, Low

] ;

Then output like this

Name	Category	field
James	Top	1
Ben	Top	1
Harry	Med	2
Sophie	Med	2
Sally	Low	3

[er_mohit · Accepted solution]

Add Dimension Name Category in straight table and add expression

Aggr(NODISTINCT RowNo(),Category)  //label Autonumber

hope it helps

[#10]

This solution works but isn't scalable at all, my actual data set has around 2,000 "Category's" do you have any further ideas? Perhaps this isn't possible... ?