Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report

Vlookup in Textbox - Step By Step

cancel
Showing results for 
Search instead for 
Did you mean: 
robert_mika
Master III
Master III

Vlookup in Textbox - Step By Step

Last Update:

Apr 23, 2015 8:16:33 AM

Updated By:

robert_mika

Created date:

Apr 23, 2015 8:16:33 AM

Attachments

Playing recently with many functions I have found out a way to get a value from a table  based on two fields

This solution will work like Excel INDEX/MATCH/MATCH with hard coded vertical dimension and dynamic horizontal dimension.

Very useful when you have  large data set but want to return only one value per category

Data Model


(Copy and Pasted below code into Edit Script window and reload)


t1:

Load * inline [

Helptext, Name, City, Salary ,Sales

Block, Bob, Warsaw, 100, 25

Color, John Doe, Budapest, 200, 358

Topics, Jenny, London, 300, 400

Font, Curt, Cair, 400, 800

Data ,Martin, Madrit, 500, 1500

Group ,Mirek, Mins, 600, 200

Total ,Stan ,Utah, 700, 158

]

;

t2:

load * inline [

Category

Helptext

Name

City,

Salary,

Sales

]

First table is our Data Table the second works as Headers of the dimensions.

Create two ListBoxes:

23-Apr-15 4-28-49 PM.jpg

'Category' and 'Name'

23-Apr-15 3-59-45 PM.jpg

create Textobject:

23-Apr-15 4-01-18 PM.jpg

and add this expression into Text window:

=if( (GetSelectedCount(Category)+ GetSelectedCount(Name))<2
,'Please choose  value from two fields',
fieldvalue(GetFieldSelections(Category)
,
FieldIndex( 'Name', GetFieldSelections(Name))))

Press 'OK'

Your result should be similar to this:

23-Apr-15 4-06-46 PM.jpg

What's does the formula do:

GetFieldSelections(Name) - is returning value (Name) from the Name field    'Jenny'

Let say we have choose Jenny

23-Apr-15 4-11-05 PM.jpg



FieldIndex( 'Name', GetFieldSelections(Name))                     will return position of Jenny in the Name field

                         =

FieldIndex( 'Name', GetFieldSelections(Jenny)) = 3



GetFieldSelections(Category) this will return value from Category Field ='City'


23-Apr-15 4-17-06 PM.jpg

fieldvalue(GetFieldSelections(Category) ,FieldIndex( 'Name', GetFieldSelections(Name)))

                                                                         =

fieldvalue(GetFieldSelections('City') ,FieldIndex( 'Name', GetFieldSelections('Jenny')))

                                                                     =

                                                                  London

23-Apr-15 4-20-57 PM.jpg


which we can check in straight table:

23-Apr-15 4-24-30 PM.jpg



The rest of the code is a wrapper which will ensure that only when value from two fields are choosen the result will be displayed.


Thanks:

sergey.makA8

for simplification:


=IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',$(=Category))


HaimantaDawar

for pointing that this expression works only with unique values


For multiple values use:

sergey.makA8

=IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',Concat(DISTINCT $(=Category),', '))


Comments
SergeyMak
Partner Ambassador
Partner Ambassador

Very interesting, but

I think it's easier just use the following expression

=IF(isNull(City),'Please select Category and Name',City)

If you don't need message 'Please select Category and Name'

= City

would be enough

Regards,

Sergey

robert_mika
Master III
Master III

The idea here is to have dynamic Category field.

Your solution works only when the Category is 'City'

SergeyMak
Partner Ambassador
Partner Ambassador

What do you think about the following expression for the textbox?

=IF(isNull($(=Category)),'Please select Category and Name',$(=Category))

robert_mika
Master III
Master III

Does it work for you?

SergeyMak
Partner Ambassador
Partner Ambassador

Almost...

It works if you modify it a bit, but still a bit simplier

=IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',$(=Category))

robert_mika
Master III
Master III

Good one.

It is simpler.

Not applicable

It is not working if my inputs are repeated. Eg. for city if I have same city name for two or more persons then only for the 1st person its showing

robert_mika
Master III
Master III

Hi Dawar,

Thank you for your input.

This has not been designed to work on multiple values - for that we would probably need a custom function in VBS or JS.

I will add this comment to the docs.

SergeyMak
Partner Ambassador
Partner Ambassador

Hi Haimanta,

Try this expression for a textbox

=IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',Concat(DISTINCT $(=Category),', '))

It's getting more complicated

Not applicable

Sergey,

Its working smooth! But do you mind elaborating this part  "Concat(DISTINCT $(=Category)" a bit

Version history
Last update:
‎2015-04-23 08:16 AM
Updated by: