QlikView documentation and resources.
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
(Copy and Pasted below code into Edit Script window and reload)
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
load * inline [
First table is our Data Table the second works as Headers of the dimensions.
Create two ListBoxes:
'Category' and 'Name'
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))))
Your result should be similar to this:
What's does the formula do:
GetFieldSelections(Name) - is returning value (Name) from the Name field 'Jenny'
Let say we have choose Jenny
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'
fieldvalue(GetFieldSelections(Category) ,FieldIndex( 'Name', GetFieldSelections(Name)))
fieldvalue(GetFieldSelections('City') ,FieldIndex( 'Name', GetFieldSelections('Jenny')))
which we can check in straight table:
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.
=IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',$(=Category))
for pointing that this expression works only with unique values
For multiple values use:
=IF((GetSelectedCount(Category)+ GetSelectedCount(Name))<2,'Please select Category and Name',Concat(DISTINCT $(=Category),', '))
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'
would be enough
The idea here is to have dynamic Category field.
Your solution works only when the Category is 'City'
What do you think about the following expression for the textbox?
=IF(isNull($(=Category)),'Please select Category and Name',$(=Category))
Does it work for you?
It works if you modify it a bit, but still a bit simplier
It is simpler.
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
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.
Try this expression for a textbox
It's getting more complicated
Its working smooth! But do you mind elaborating this part "Concat(DISTINCT $(=Category)" a bit