topic Is there similar functionality to Excel's VLOOKUP? in QlikView

Is there similar functionality to Excel's VLOOKUP?

Thu, 03 Mar 2011 22:27:57 GMT

I am trying to convert an Excel report to QV, and I am having trouble with the part where I am remapping some fields that are using a VLOOKUP.

For example, let's say I have "Product Market" and "Sales Market" that have slightly different values. I want my Product Market to be remapped to equal the Sales Market values.

Product Market = Chicago Downtown, New York Airport, etc...

Sales Market = Chicago, New York, etc...

The VLOOKUP table in Excel has the Product Market mapped to the appropriate Sales Market, so in the end my output has Product Market = Chicago, New York, etc...

I can get this to work using a reference table or hard coding some of the mapping in the data pull, but I have a huge amount of data and there are several hundred possible values that need to be mapped. Is there an easy way to do this without causing performance issues & taking forever to set up manually?

Is there similar functionality to Excel's VLOOKUP?

Anonymous — Thu, 03 Mar 2011 22:44:44 GMT

Christine,
I think that mapping load in combination with applymap() is the closest to your needs. You can find deatils in the QV Help and in reference.

Is there similar functionality to Excel's VLOOKUP?

Thu, 03 Mar 2011 23:48:30 GMT

Hi Christine

There is a lookup function:

Eg:

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

However if you are making the lookup in the script you may want to look at "left joining" the data ?

Is there similar functionality to Excel's VLOOKUP?

Fri, 21 Oct 2011 21:50:00 GMT

Is there a post-script equivalent? I need to compare to the results of OSuser(), which won't be known until viewing time, post-reload.

Re: Is there similar functionality to Excel's VLOOKUP?

Miguel_Angel_Baeyens — Sat, 22 Oct 2011 11:38:55 GMT

Hi,

Since you already have all your tables and fields, my guess is that your code would look like the following:

Match(Upper(OSUser()), 'DOMAIN\GRANTEDUSER1', 'DOMAIN\GRANTEDUSER2')

So if the user is DOMAIN\GRANTEDUSER1 will have access or see whatever you are hiding. But if the user logged in is DOMAIN\ADMINISTRATOR will not see the object with the condition above.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Is there similar functionality to Excel's VLOOKUP?

Sat, 22 Oct 2011 16:39:30 GMT

Thanks Miguel,

I did end up solving our issue, using the following code:

=sum(if(wildmatch(User.Name,Upper(ltrim(rtrim(subfield(osuser(),'\',2))))),1,0))

My disconnect was that, in the Help file, the MATCH() and WILDMATCH() functions appear to only compare to a hard-coded horizontal array of values, not a column/field. But, using them against a field (User.Name in this case) works just fine.

Thanks for your help!

-DJ