Lookup Function in a Text Object

[nesqlik]

Hi Qlik Community,

I've been searching for a way to work this for some time now and haven't been successful.

I have a dashboard that needs an addition.  My end users want to see tiles with 'Position Title' and the 'Employee Name' who holds the position.  The position will stay static for a long time, but the employees will shuffle in and out.  The records in my table look like this:

Position Code, Position Title, Employee Name, etc.  

Is there a way where I can use a Text Object to return 'Employee Name' where 'Position Code' is equal to x?  Essentially this is a vlookup in Excel, but I'm not sure of how to perform this in QlikView.

Thank-you!

[Or]

You should be able to use a simple set analysis or if() in your text object.

Only(<{[Position Code] = {'x'} }> [Employee Name])

(If you might have multiple employees, this will not work - you will have to either concatenate them, or use a filter to only get the last / active one)

[NZFei · Accepted solution]

If you know that for each position, there is only one employee, you can use this expression in the text object:

=only({<Position Code]={x}>}[Employee Name])

If there are more than one employees, you can list them by using this:

=Concat(DISTINCT if([Position Code]='x', [Employee Name]),',')

[nesqlik]

Hi thank-you!

I think Only would work here as the position code is tied to only one employee in our database.

I plugged the below expression into the Text Object but it's giving me an error that ')' is expected.  Does the way I wrote this leave off the last ')'?

=Only(<{ [IncumbentPositionCode] = {'50141373'}} > [IncumbentLastName])

[NZFei]

=Only({<[IncumbentPositionCode] = {'50141373'}>} > [IncumbentLastName])

[nesqlik]

Fantastic!  The only formula here was able to solve the expected ')' error in my expression.

Thanks again!