topic Re: Using Lookup to set the value of a variable in QlikView

Using Lookup to set the value of a variable

paul_scotchford — Tue, 04 Mar 2014 22:02:47 GMT

Hi All,

I'm trying to lookup a field value based on a user selection, I am using Lookup() , but I'm guessing I'm doing something incorrect as I cannot see this working.

This is being used outside the context of a load, hence am I working outside the scope of this function requirement , I am trying to set the variable dynamically , see below...

=if(max(CalendarDate) = lookup('CurrentTo', '[Country Code]', GetFieldSelections([Country Code]) , 'DateNow'),1,0)

Thanks in advance

Paul

Re: Using Lookup to set the value of a variable

sbaldwin — Wed, 05 Mar 2014 07:46:35 GMT

Hi, It will never work, lookup is a script function only, you'll need to think of a different way.

Re: Using Lookup to set the value of a variable

sunilkumarqv — Wed, 05 Mar 2014 07:50:52 GMT

Hi Steve,

lookuphttp://michaelandqlikview.blogspot.in/2013/06/qlikview-lookup-function.html http://michaelandqlikview.blogspot.in/2013/06/qlikview-lookup-function.html( fieldname, matchfieldname, matchfieldvalue [, tablename] )

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the fieldmatchfieldname.

Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned.

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

I occasionally have issues using this function - mainly as I try to avoid it anyway. Couple of things from trial and error:

The field names with the ' ' literal wrapper. DO NOT wrap them with [ ] inside the ' ' even if the field usually needs them. It will confuse Qlikview and won't find your match.
As it says, should always use the tablename, as usually not trying to use the current table name.

Re: Using Lookup to set the value of a variable

Wed, 05 Mar 2014 19:15:50 GMT

Hey Paul,

Can you provide a little more details.

What value should the variable hold
Based on what condition

A sample file would also be very helpful.

Also as mentioned by Steve Lookup() can't be used on the Front-end.

Thanks

Re: Using Lookup to set the value of a variable

paul_scotchford — Wed, 05 Mar 2014 21:29:52 GMT

Correct, Yes I found another way where I could avoid a lookup completely.

Thanks

Re: Using Lookup to set the value of a variable

mohan_1105 — Tue, 30 Apr 2019 16:46:29 GMT

Hi Paul,

It would be great if you could tell me what is the way to write the expression. I'm facing the same issue

Re: Using Lookup to set the value of a variable

chris1987 — Wed, 01 May 2019 08:46:46 GMT

Hi Paul,

I've had similar situations and ended using the Only() function:

e.g.

I needed to find the year from my MasterCalandar based on yesterdays date:

=only({<DateID={'$(=num(today())-1)'}>}[Acct Year])

So you can see that I'm returning [Acct Year] where my DateID is yesterday.

I'm sure you can adapt this quite easily to suit your needs?

Cheers

Chris

Re: Using Lookup to set the value of a variable

greenee — Tue, 13 Feb 2024 09:29:07 GMT

Thanks for the pointer Chris,

I have a little table with NTNAME and Employee_Country, and used the following variables to detect Country of current user, so I could use in a show hide expression on a set of tables, where users in certain countries can only see certain columns.

v_OSUSER = OSUser() ;

v_User_Country = only({<NTNAME={'$(=$(v_OSUSER))'}>}EMPLOYEE_COUNTRY) ;

Show hide condition = If ( Match(Upper($(v_User_Country)),'XXX','YYY')>0,1,0)

Regards Greenee

topic Re: Using Lookup to set the value of a variable in QlikView

Using Lookup to set the value of a variable

Re: Using Lookup to set the value of a variable

Re: Using Lookup to set the value of a variable

Hi Steve,

lookuphttp://michaelandqlikview.blogspot.in/2013/06/qlikview-lookup-function.htmlhttp://michaelandqlikview.blogspot.in/2013/06/qlikview-lookup-function.html( fieldname, matchfieldname, matchfieldvalue [, tablename] )

Example:

lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

Re: Using Lookup to set the value of a variable

Re: Using Lookup to set the value of a variable

Re: Using Lookup to set the value of a variable

Re: Using Lookup to set the value of a variable

Re: Using Lookup to set the value of a variable

lookuphttp://michaelandqlikview.blogspot.in/2013/06/qlikview-lookup-function.html http://michaelandqlikview.blogspot.in/2013/06/qlikview-lookup-function.html( fieldname, matchfieldname, matchfieldvalue [, tablename] )