Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
jessica_webb
Contributor III

Alphabetical field index

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

1 Solution

Accepted Solutions

Re: Alphabetical field index

Try this

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


Capture.PNG

View solution in original post

6 Replies

Re: Alphabetical field index

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

Re: Alphabetical field index

Rank seems to be going the opposite

Re: Alphabetical field index

May be this

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

Capture.PNG

jessica_webb
Contributor III

Re: Alphabetical field index

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

Try this

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


Capture.PNG

View solution in original post

jessica_webb
Contributor III

Re: Alphabetical field index

Perfect! Thank you so much