Vlookup expression

[#1]

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

[Anonymous]

Hi Edward,

Do you have a sample copy of the QVW file?

[#3]

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

[#4]

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))

[richard_pearce6]

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

Richard

[satishkurra]

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

[#7]

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?

[richard_pearce6]

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

[#9]

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.

[#10]

What does your larger expression look like?

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