Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chrisellis
Contributor

Add a new column based on criteria.

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

4 Replies
MVP
MVP

Re: Add a new column based on criteria.

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

chrisellis
Contributor

Re: Add a new column based on criteria.

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

Highlighted
MVP
MVP

Re: Add a new column based on criteria.

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?

chrisellis
Contributor

Re: Add a new column based on criteria.

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