topic Re: Alphabetical field index in QlikView

Alphabetical field index

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

I have a field in my script called 'Name' with following values:

John

Mark

Aaron

Tomas

Benjamin

I cannot change the load order.

I then have a report with a separate section for each name. In my title, I am using the following:

=Name &' - '&FieldIndex('Name', Name)&'/'&FieldValueCount('Name')

The problem is, whilst my report loops through 'Name' alphabetically (which is what I want), the field index is based on sort order, so I get the following titles:

Aaron - 3/5

Benjamin - 5/5

John - 1/5

Mark - 2/5

Tomas - 4/5

What I want is:

Aaron - 1/5

Benjamin - 2/5

John - 3/5

Mark - 4/5

Tomas - 5/5

Any ideas? Needs to be totally dynamic as I will never know what the names are, nor how many different names there will be.

Thanks,
Jess

Re: Alphabetical field index

sunny_talwar — Thu, 14 Sep 2017 11:28:29 GMT

What if you try using Rank(Name)? Does that solve your issue?

Re: Alphabetical field index

sunny_talwar — Thu, 14 Sep 2017 11:29:50 GMT

Rank seems to be going the opposite

Re: Alphabetical field index

sunny_talwar — Thu, 14 Sep 2017 11:32:06 GMT

May be this

=Name &' - '&(FieldValueCount('Name') - Rank(Name) + 1)&'/'&FieldValueCount('Name')

Re: Alphabetical field index

jessica_webb — Thu, 14 Sep 2017 12:58:13 GMT

Hi Sunny,

Thanks for the suggestion. Unfortunately, it doesn't work outside of the table.

If you see the attached, I've added a text box with your expression. When I select any name in the list box, it always gives me 5/5

Re: Alphabetical field index

sunny_talwar — Thu, 14 Sep 2017 13:00:35 GMT

Try this

=Aggr(Name &' - '&(FieldValueCount('Name') - Rank(Only({<Name>} Name)) + 1)&'/'&FieldValueCount('Name'), Name)

Re: Alphabetical field index

jessica_webb — Thu, 14 Sep 2017 13:02:46 GMT

Perfect! Thank you so much