4 Replies Latest reply: Jul 6, 2016 12:24 AM by Christopher Ellis RSS

    Add a new column based on criteria.

    Christopher Ellis

      Could anyone help.

       

      I am looking at updating a table with a new columns based on criteria in the target table.

       

      Table A

      The example shows grades and subjects that fit into a particular basket.

       

      SubjectGradeBasket Number

      English

      41
      English Lit31

       

      Table A Updated

      Update table A with the highest grade for Basket 1 then use the remaining grade in Basket 2

       

      SubjectGradeBasket NumberBasket 1Basket 2

      English

      414
      English Lit313

       

      Table A is already loaded in QlikSense version 3.

       

      I am looking at expanding this to use more subjects.

       

      Many Thanks

       

      Chris

        • Re: Add a new column based on criteria.
          Sunny Talwar

          Would you be able to add few more rows of data to understand the pattern that is taking place here?

            • Re: Add a new column based on criteria.
              Christopher Ellis
              PupilSubjectGradeBasket NumberBasket 1Basket 2Basket 3Basket 4
              Pupil AEnglish414
              Pupil AEnglish Lit313
              Pupil AMaths525
              Pupil AGeography636
              Pupil AScience737
              Pupil AHistory434
              Pupil A

              Spanish

              535
              Pupil AFrench636
              Pupil AScience Additional232
              Pupil ATextiles646
              Pupil ADrama242

               

              Hi Sunny,

               

              Basket 1 : Can only use English,English Lit the highest of the 2 grades goes to basket 1 and the other goes to basket 4.

              Basket 2 : Contains Maths, because only one subject fits into this basket it goes to basket 2.

              Basket 3 : Contains Geography,Science,History,Spanish,French,Science Additional, The top 3 grades go to basket 3 and the others go to                  basket 4.

              Basket 4 : Contains Textiles and Drama also basket 4 contains the remainder subjects from the other baskets only the top 3 subjects are                  needed to be kept in basket 4.

               

              This then moves onto pupil B and the process is repeated, the principle of sorting the baskets out is the same in each case but the subjects may be different.

               

              Many Thanks

               

              Chris

                • Re: Add a new column based on criteria.
                  Sunny Talwar

                  Earlier English Lit was in Basket 2, why did it move to Basket 4? I am still not sure if there is a logic that determine which bucket the number needs to go in or do we have the pre-set conditions available to us?

                    • Re: Add a new column based on criteria.
                      Christopher Ellis

                      HI Sunny,

                       

                      The first example was only to illustrate the principle of how English could work. The more detailed example is how the end result would look.

                       

                      The basket column is predetermined, a lookup is performed on the subject column to determine which basket it should be in.

                       

                      Because the baskets can only hold so many subjects not all subjects will stay in the original basket they had been assigned because they may not have a high enough grade. This then puts them by default into the last basket 4.

                       

                      Basket 1 : Is only allowed 1 subject out of all the subjects assigned to basket 1, this is the highest grade.

                      Basket 2 : Is only allowed 1 subject out of all the subjects assigned to basket 2, this is the highest grade.

                      Basket 3 : Is only allowed 3 subjects out of all the subjects assigned to basket 3, this is the highest 3 grades.

                      Basket 4 : Is only allowed 3 subjects out of all the subjects assigned to basket 4 (this includes the ones that have moved into basket 4 that do not fit into there original basket), this is the highest 3 grades.

                       

                      Hope this makes a bit more sense.

                       

                      Many thanks

                       

                      Chris