Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
I'm trying to figure out how to show text in a textbox based on the selected items from a table. The data I'm working with are setup like this:
Indicators Table
Indicator_ID | Indicator_Name | Definition_ID |
---|---|---|
1 | Test Indicator One | 1 |
2 | Test Indicator Two | |
3 | Test Indicator Three | 1 |
4 | Test Indicator Four | 2 |
Definitions Table
Definition_ID | Definition | Definition_Source |
---|---|---|
1 | Some text for a definition One | Example Source |
2 | Some text for a definition Two | Example Source |
On my sheet I have a table listing the Indicator_Name field and a textbox with an expression that returns the Definition field.
What I need to happen is when multiple entries in the table are selected, the textbox will show the definition if the IDs of the selected items match, otherwise the textbox should be blank. This works for entries that have a definition ID, but not in the case where there is no ID. If the entry with no definition ID is selected along with one that does, the definition of the one that does shows in the textbox. I need it to show blank, because technically the definition IDs don't match.
I tried something like this for the textbox expression but to no avail.
if(COUNT({$<Definition_ID={"$(Len(Definition_ID)) = 0"}>}Definition_ID) > 0, '', Definition)
Suggestions?
Hello Jason,
I am not sure that I understand, but I will give it a try. In the data model the two sets of data (Indicators table and Definitions table) are connected right?
So, to get the text of the currently selected definitions: concat(Definition,',')
Then you say if the indicator without a Definition_ID is selected then everything should be blank?
=if(index(concat('|' & Definition_ID & '|',','),'||')>0,'',concat(Definition,','))
Hello Jason,
I am not sure that I understand, but I will give it a try. In the data model the two sets of data (Indicators table and Definitions table) are connected right?
So, to get the text of the currently selected definitions: concat(Definition,',')
Then you say if the indicator without a Definition_ID is selected then everything should be blank?
=if(index(concat('|' & Definition_ID & '|',','),'||')>0,'',concat(Definition,','))
Not quite, but this led me down the right path. The expression ended up looking like this:
=if(index(concat(distinct '|' & Definition_ID & '|','|'), '||') > 0, ' ', Definition)
What I needed was to display the definition (a single definition) only if all definition IDs of the selected items match. So by using distinct, the concatenated list only contains 1 instance of the ID if the selected items have a matching ID. If there are multiple items in the concatenated list (different IDs), or if the item with a blank ID was selected, then the index is greater than zero and it shows ' '.
Sorry for not being more clear in my question. I'm new to Qlik and discovering that dealing with blank/empty values is a bit tricky. Thanks so much for your help Morgan!