topic Re: RowNo() Help in QlikView

RowNo() Help

Wed, 05 Feb 2014 11:06:25 GMT

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?

Re: RowNo() Help

Wed, 05 Feb 2014 11:09:56 GMT

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.

Re: RowNo() Help

Wed, 05 Feb 2014 11:12:25 GMT

Thanks for your response,

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

Re: Re: RowNo() Help

Wed, 05 Feb 2014 11:17:29 GMT

Hi Wallerjc,

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

Re: RowNo() Help

Wed, 05 Feb 2014 11:20:30 GMT

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

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

Fabrice

Re: RowNo() Help

Wed, 05 Feb 2014 11:26:43 GMT

in script write this

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

Re: RowNo() Help

Wed, 05 Feb 2014 11:30:41 GMT

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

Re: RowNo() Help

Wed, 05 Feb 2014 11:50:47 GMT

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

Re: RowNo() Help

er_mohit — Wed, 05 Feb 2014 12:03:55 GMT

Add Dimension Name Category in straight table and add expression

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

hope it helps

Re: Re: RowNo() Help

Wed, 05 Feb 2014 12:04:39 GMT

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... ?

Re: Re: RowNo() Help

Wed, 05 Feb 2014 12:04:43 GMT

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... ?

Re: Re: RowNo() Help

Wed, 05 Feb 2014 12:04:57 GMT

I need to do this outside of the load script, although i thank you for your time

Re: Re: RowNo() Help

Wed, 05 Feb 2014 12:05:49 GMT

Ding ding ding, we have a winner!

I can't believe I didn't think to simply Aggr() the table, many thanks for your time and solution here.

James.

Re: RowNo() Help

Wed, 05 Feb 2014 12:19:56 GMT

hi wallerjc

my last post fullfill your requirment. because this code

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

work on any number of records.

and expression posted by Mohit Sahrma is

also well.

Regards