2 Replies Latest reply: Dec 15, 2015 8:17 AM by Jay Fitchett RSS

    Set text based on selected data?

    Jay Fitchett

      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_IDIndicator_NameDefinition_ID
      1

      Test Indicator One

      1
      2Test Indicator Two
      3Test Indicator Three1
      4Test Indicator Four2

       

      Definitions Table

       

      Definition_IDDefinitionDefinition_Source
      1Some text for a definition OneExample Source
      2Some text for a definition TwoExample 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?

        • Re: Set text based on selected data?
          Morgan Kejerhag

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

          • Re: Set text based on selected data?
            Jay Fitchett

            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!