3 Replies Latest reply: Aug 30, 2018 7:56 AM by Olivier GAUTIER RSS

    Dimention value - If not in other list

    Jeremey Wise

      Picking Qlik Sense back up after being away for a year..


      Trying to make a simple pie chart.. 


      I have two spreadsheets in Qlik Sense:






      Dim1 (field)

      =(FruitTable1  if not listed in BadFruit)


      AltDim1 (field)

      =(FruitTable2 if listed in BadFruit where BadFruitCatagory="weed")


      AltDim2 (field)

      =(FruitTable2 if listed in BadFruit where BadFruitCatagory="vine")


      Goal is to end up listing "how many <let user select fruit and by type> of each catgory are sold and later on in diferent pie chart at that cost.



      Meanwhile I will slog through the training videos again to refresh.

        • Re: Dimention value - If not in other list
          Olivier GAUTIER



          The best will be to make a link between two tables in script

          and have same name for fruit with a flag for bad fruit


          with this model :


          for case 1:

          if (fruittable1 <> badfruit, fruittable1) as dimension

          but it's not efficient



            • Re: Dimention value - If not in other list
              Jeremey Wise

              My goal is to almost build a master dimension list. The data I get does not have a category column so I have to find a way to create a dimension: (I would have t set of four or more master dimensions)


              Ex:  MasterDimBadFruit_vegitable

              IF FruitTable1 = 'tomato' or 'artichoke'

              Ex:  MasterDimBadFruit_vine

              IF FruitTable1 = 'watermelon' or 'cantaloupe'




              But that formula is not a way to create a dimension and invalid as it lacks a "then" .   I want to write a formula that creates a master dimension based on a list.


              I tried to create the second spreadsheet as 'master table' as I could teach sales to easily build and save by tab 'categories' they want to group but like you said... how this then can be set as a dimension value when searching the main database is what I don't know how to do.


              Most categories they will want to group are four of five parts.. others are hundred....hence the list vs VERY long string of 'or...'