5 Replies Latest reply on Sep 5, 2017 9:31 AM by Stephen Kimel

# Retrieve value of string variable based on position in sorted order

If there is a string variable called fruit like ("Apple", "Banana", "Pear"), is there a way to retrieve values based on the position that the value is in the sorted order of the string variable? I understand that I can do

if [Fruit] = {fixed: max([Fruit])} then [Count]

END

and this will sum up all the counts where the fruit column is equal to last item in the sorted order of the Fruit variable (in the case above that would be Pear). Is there a way to say that I want to sum up the [Count] variable based on the second to last value in the sorted order of the [Fruit] variable? For example, I want to sum up all counts for Pear but I don't want to just put a filter in for Pear (this filter would need to be changed frequently as the values in the variable change frequently).

The reason that I want to automatically find the value based on the position of the sorted order of the string variable is because this variable changes frequently and I want to show visuals based on which fruits are in certain positions. The actual application is not fruits, but a string that is alphanumeric, and because of how it is sorted, choosing the variable based on position in sorted order would work.

What my logic would be in words is: find the max value for this variable and the go back one (or two or three).

I have searched the site for a solution to this and have only found a way to retrieve the first and last value of a string variable (as shown above).

The workbook was created in 10.1.8

• ###### 1. Re: Retrieve value of string variable based on position in sorted order

You can set up an index variable that knows its position in the table.  And then you can go backward from there 2 or 10 or whatever positions.

I'll play with your workbook and do something.

• ###### 2. Re: Retrieve value of string variable based on position in sorted order

(V 10.1 here)

See attached.

On sheet 2 I added a calc called [index].  It displays the positional index in the table.  On a simple sheet like this one, using the default setting of TABLE(down) gives the right layout of index.  I also created a calc called [What index did I pick?].  And I display that value in the title so you can see how you can access that index value.  Once you know an index value you can do all sorts of harvesting from the table based on it.

On sheet 3 I changed [Count] from a measure to a dimension just so that you can see how addressing in table calcs works.  I know [Count] as a dimension doesn't make sense, but I needed another dimension to demonstrate this.  See what [index] does now?  It's still set to TABLE(down), so you just get a count of 1-through-N of all the rows on the sheet.  Notice also that the value of [What did I pick] has multiple values.  That's because every Fruit has multiple rows.

On Sheet 4 I changed the addressing of the two calcs to work "at the level" of [Fruit].  Now you get a unique number for each fruit, and it is repeated for each [Count] under that fruit.

• ###### 3. Re: Retrieve value of string variable based on position in sorted order

Hi Joe,

This helps a lot. Do you know if there is anyway to hide rows or columns based on the index that you made? I would like to have only certain rows appear (say those with index 3 and 5), and the rest hidden from view. I know that I can manually do this, but is there a way to do this automatically?

• ###### 4. Re: Retrieve value of string variable based on position in sorted order

If you use a table calc as a filter, it won't change the rows in the underlying table, but it will control what parts of the table to display.

LOOKUP() is a table calc.  Using LOOKUP on offset = 0 is a common way to make a table calc to control the display. (Offset = 0 says to satisfy the equation for each current row.)

So in the attached example I made a simple filter using LOOKUP.  It makes a list of values just like any other filter, and you can select any or all values to display.

If you don't want the user doing the selection, you can control it.

LOOKUP( [index] , 0 )

do

LOOKUP( IF [index] = 3 or [index] = 5 then 1 else 0 END , 0 )

This one will result in a value of either 1 or 0 for each [index] value, and you drag that to your filter shelf and select for value =1 and it will display rows for 3 and 5.

• ###### 5. Re: Retrieve value of string variable based on position in sorted order

That is what I was looking for. Thank you very much for your help! I marked your original answer as the correct answer, but that along with the LOOKUP function got me to where I wanted to go.