6 Replies Latest reply: Jan 23, 2018 10:55 AM by Erica Whalley RSS

    How to use FieldSelections to pull value from seperate table

    Jason Turner

      Hi there,

       

      I am trying to build a visualisation in one of my Qlik Apps which essentially does the following, I will have a drop down list of advisers, for example:

       

      Advisor
      AA
      BB
      CC
      DD
      EE
      FF

       

       

       

      I will then have a seperate table with targets, but this can also include combined targets for 2 advisors, or more. Example being:

       

      AdvisorSelectionTarget
      AA0.5
      AA,BB0.7
      AA,BB,CC0.8
      AA,BB,CC,DD0.9
      CC,DD0.6
      BB0.7
      CC0.8
      DD0.9
      EE1

       

       

       

      now it would be good noting there is no formula relating to AA's target and BB's - or they are not a calculated version but essentially depending on the selection on the app you use - it would bring up the selection for this target.

       

      At the moment i can use GetFieldSelections(Advisor) so if i click AA & BB on the dropdown i get "AA, BB"

       

      I would then like to look up this seperate table which is not joined to other data tables and bring through the target value. I have tried using FieldValue and FieldIndex but am not getting it to work, and Peek/ match functions are script only. This is for the chart side so any ideas would be greatly appreciated. It would also be good noting i'm only interested in if the values match something in the table so if i select something not in AdvisorSelection im happy for it to show blank aswell. Hopefully this makes sense?

        • Re: How to use FieldSelections to pull value from seperate table
          Erica Whalley

          You could return the value through set analysis.

           

          If you put the string into a variable, you can pass this into set analysis to return the one value that is associated with the string of names.

           

          so set a variable vAdvisorNames=concat(distinct Advisor,',')

           

          Then use this in the expression to pull the required value out of the table, if there are no matches it should return null

           

          =Only({<AdvisorSelection={"$(vAdvisorNames)}>} Target)

           

          Erica