Lookup function is not in list of functions

[#1]

Hi all.

I am using Qlikview version 9.00.7320.7 SR2. I want to use the Lookup function but it is not in the list. See screenshot attached.

Am I missing something?

imagine i have a table as follows

Name: Indicators
Field 1: Indicator Code
Field 2: Indicator Name

I want to use the following function: lookup('Indicator Code','Indicator name','1','Indicators')

It doesn't recognise the function 'lookup'

please help

fil

[#2]

Hi fil

Lookup is only available in the script not in chart because it's a transfer from a table to another table.

Maybe you can explain the chart you want to display

JJ

[#3]

The chart is designed to display the figures a user-selected Indicator. The user selects the indicator by clicking a textbox which has an action setting a variable called ChosenIndicator.

There is a table that has the Indicator Code and Indicator Name. If the user clicks on the 5th KPI, the action will assign 5 to the variable ChosenIndicator and another action will restore the chart. I want the chart to lookup the correct Indicator Name for its Title using the ChosenIndicator variable to lookup it up.

I know that's not brilliantly explained. Does it make sense what I'm trying to achieve. Basically, where in Qlikview does one use the Lookup function, if it isn't in the EXpression Dialog box.

thanks in advance

fil

[#4]

If i understand well, the user have to choose an Indicator code and you want to display Indicator name as a title in a chart ?

If the relation between Code & Name is unique , it's directly possible to write in the window title :

[Indicator Name]

JJ

[#5]

Hi Jean

That's right. However I have designed my report so all Indiciators are on show. They show their value according to a sum or avg (depending on the KPI) of the value of all the selected Practices.

In the KPI charts I use code such as this for Indicator 1:

=sum(if([Indicator Code] = 1,[Value]))

to only sum the values for Indicator 1

How do I get the Indicator Name?

Can you have a look at my attached qvw file please. I've set it so when the user clicks on the 1st set of traffic lights, it brings up a chart which I want to set the title for dynamically. I've also tried using MinString.

Have a look in the VBScript where I have tried to use lookup

Thanks for your help

[#6 · Accepted solution]

Also, why is the lookup function in the list of Inter-Record functions on page 415 of the QlikView Reference Manual.pdf document, but not in the list of functions when i look for it in the Expression Dialog (see screenshot in my earlier post)

Anyway, I've found a workaround. i put this code in the chart title:

=minstring(if([Indicator Code] = 1,[Indicator Name]))

cheers

fil

[#7]

I'm trying to do something similar, although I'm not sure if it's a good idea or not. I have people viewing data for their organizations and they want to compare averages against the next two levels in a hierarchy. So I have a chart with four columns: Department Name, Department Average, Division Average, Corporate Average.

The challenge is that different departments will access the same application, so the calculation in the Division Average column needs to change based on which Department has logged in. I know the identity of the user via the QVUSER() function, which I've used with great success in other flavors of this app. But I can't seem to dynamically determine the Division in my set analysis expression based on the QVUSER() of the Department. Here's an example of the code I've tried to use, among other variations, none successful:

avg({$ <Division = {(if([DeptCode] = $(=QVUSER()), [DivisionName], 'N/A'))}>} SomeValueToCalculate)

I've seen where people have used macros for similar functionality, but we are using an AJAX client exclusively so I think that is not an option. If you can prove me wrong, please do. Any and all suggestions are appreciated.

Regards,

Jim

[#8]

Bump.