No lookup function in QV11?

[#1]

Hi all,

I'm not sure if I'm missing something here, but I don't have the ability to use the lookup() function (see attached screenshots). I'm running version 11.20.12904.0 SR12. Has anyone else encountered this and know of a workaround?

Many thanks,

Geoff

[rwunderlich · Accepted solution]

lookup() is a script only function. It's usually unnecessary in the UI as the associative model does the "lookup" automatically. There are cases however, and other functions available. Can you describe what you are trying to accomplish?

-Rob

[#3]

Hi Rob,

Many thanks for the reply--that certainly makes sense as to why I found the function in the reference manual but wasn't seeing it in the expression editor. I think I have found a workaround using FieldIndex() and FieldValue(), but it is a bit clunky. Here's a simplified version of what I'm trying to accomplish:

I need to create an expression that returns the survey item text that corresponds with a unique survey question ID. I have a table like the below where QuestionID is the unique ID and QuestionText is the actual survey question that corresponds to the unique ID.

QuestionID, QuestionText

1, <question 1 survey text>

2, <question 2 survey text>

Currently I'm using FieldIndex() on QuestionID to return the row number associated with the QuestionID in question and then using the result of that expression in a FieldValue() expression on QuestionText to return the corresponding survey text associated with the QuestionID. I'm not sure if there's a better way of doing this, but would definitely be open to your thoughts. An example of my expression is below:

=FieldValue('QuestionText',(FieldIndex('QuestionID','<desired QuestionID>')))

Best,

Geoff

[rwunderlich]

It would be interesting to see a screen shot of your table model. If everything is properly linked, you should be able to use QuestionText as a Dimension directly in a chart without doing a lookup.

-Rob

[#5]

Hi Rob,

Many apologies for the delay--was busy and then the holidays hit. Below is a picture of my Table Viewer in case that's helpful. I'm now hitting a snag with a different application of the FieldIndex() FieldValue() solution I mentioned above.

What I'm now trying to do is return an "Index" value associated with a given "QuestionID". I have a table like the below with "QuestionID" in one column and "Index" in the other. The "Index" variable is a rollup variable--in other words multiple questions make up a single index. What I need is an expression where I put in "3" (for example) and it spits out "Green". My FieldIndex() FieldValue() solution is not working because FieldValue() only counts unique values instead of all the values of the Index column. So, FieldValue('Index',4) returns a null and FieldValue('Index',2) returns "Green".

QuestionID, Index

1, Blue

2, Blue

3, Green

4, Green

Would you know of a way to address this?