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:

      FruitTable1CostQty
      apples11
      pears424
      watermelon562
      grapes266
      oranges16142
      cherries345576
      watermelon12313
      tomato23769

       

       

      BadFruitBadFruitCatagory
      tomatovegetable
      eggplantweed
      watermelonvine

       

      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

          Hi,

           

          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

           

          regards

            • 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'

               

              etc.

               

              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...'