Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Is there similar functionality to Excel's VLOOKUP?

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?

5 Replies
Anonymous
Not applicable
Author

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.

Not applicable
Author

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 ?

Not applicable
Author

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.

Miguel_Angel_Baeyens

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

Not applicable
Author

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