topic Re: Lookup Function in a Text Object in QlikView

Lookup Function in a Text Object

nesqlik — Mon, 19 Nov 2018 19:52:09 GMT

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!

Re: Lookup Function in a Text Object

Or — Mon, 19 Nov 2018 19:54:26 GMT

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)

Re: Lookup Function in a Text Object

NZFei — Mon, 19 Nov 2018 20:03:19 GMT

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]),',')

Re: Lookup Function in a Text Object

nesqlik — Mon, 19 Nov 2018 20:04:50 GMT

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

Re: Lookup Function in a Text Object

NZFei — Mon, 19 Nov 2018 20:06:00 GMT

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

Re: Lookup Function in a Text Object

nesqlik — Mon, 19 Nov 2018 20:11:47 GMT

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

Thanks again!