topic Re: Vlookup expression in QlikView

Vlookup expression

Mon, 02 Jun 2014 16:20:16 GMT

Hi,

I am looking for a solution that will mimic a Vlookup in excel.

I have created a unique key within a table as an expression (has several caluculations looking at different tables). I have an excel table loaded into Qlikview that has the key and a 'value' as a seperate column. I am essentially trying to show the value from the excel table rather than my calculated key.

I have tried a simple 'if' statement

if ( expression = 'Excel Key', 'Excel Value' , Null() )

However no value is being returned.

I am sure I have resloved this before in the past however I cannot remember how I managed it (this needs to be done in a table and not the load script).

I would be very grateful for any advice.

Thanks in advance

Re: Vlookup expression

Anonymous — Mon, 02 Jun 2014 16:59:37 GMT

Hi Edward,

Do you have a sample copy of the QVW file?

Re: Re: Vlookup expression

Tue, 03 Jun 2014 12:44:55 GMT

Hi Sean,

Many thanks for your response. I have put together a quick example. I need to be able to do this via an expression in the table.

Many thanks

Re: Vlookup expression

Tue, 03 Jun 2014 13:43:45 GMT

I think I have managed to resolve the issue using the 'Only' function. I will test it out on the main data.

Only( if(Key & code = Key1, value2))

Re: Vlookup expression

richard_pearce6 — Tue, 03 Jun 2014 13:45:31 GMT

The apply map script function works in a very similar way to a vLookup

Richard

Re: Vlookup expression

satishkurra — Tue, 03 Jun 2014 13:52:07 GMT

Apply Map in Qlikview is similar to Vlookup in Excel....

Re: Vlookup expression

Tue, 03 Jun 2014 14:10:22 GMT

Thanks for the replys. Unfortunately I can't really use the apply map in the load script due to how complex the main application is (would mean I would probably have to rebuild it) I am currently generating the key in an expression which in principle should work using the 'Only' function.........At least I got it to work on the example.

In the main application the key is built using quite a long 'if' statement. Unfortunately despite knowing (and proving) the if statement creates the key, as soon as I inbed it into the 'Only' statement I get a script error.....

Back to the drawing board..... any other suggestions?

Re: Vlookup expression

richard_pearce6 — Tue, 03 Jun 2014 14:20:28 GMT

are you asking me Satish? I don't understand your reply??

Re: Vlookup expression

Tue, 03 Jun 2014 16:24:17 GMT

I found another way to do this

sum( if(Match(Key & code , Key1) >0, value2, 0))

Sadley I am having the same issue with this expression as well. As soon as I sink my if statement in to create a key, I get an error.

Re: Vlookup expression

Tue, 03 Jun 2014 16:45:35 GMT

What does your larger expression look like?

It sounds like you may have already tried something like =SUM(IF(Key & code = Key1,value2))

Re: Re: Vlookup expression

Thu, 05 Jun 2014 10:35:21 GMT

Hi Clark,

I think I have found the issue. The main key is generated by using 'Key & Code', the 'Code' part is made up from a nested 'if' statement that includes 'Sum'. Although all of this works in isolation to generate the correct key, as soon as I add it as part of the overall expression it generates an error.

I have attached an updated example to demonstrate.

Do you (or anyone else) know of a way I can preserve the sum if statement whilst getting the lookup to work?

Cheers

Re: Vlookup expression

Anonymous — Thu, 05 Jun 2014 11:36:10 GMT

Hello, Edward.

QlikView has a Lookup() function. It should do just what you need. Take a look: Lookup function, Joins and Lookups

If you don't use Lookup(), you'll need to use the Aggr() function. It allows you to make nested aggregations. It's needed becaouse the long if statement that generates the key uses Sum(). To help you with it, we'll need a more detailed sample qvd, one that is more close to your actual app.

Hope it helps.