Add a new column based on criteria.

[Anonymous]

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.

Subject	Grade	Basket Number
English	4	1
English Lit	3	1

Table A Updated

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

Subject	Grade	Basket Number	Basket 1	Basket 2
English	4	1	4
English Lit	3	1		3

Table A is already loaded in QlikSense version 3.

I am looking at expanding this to use more subjects.

Many Thanks

Chris

[sunny_talwar]

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

[Anonymous]

Pupil	Subject	Grade	Basket Number	Basket 1	Basket 2	Basket 3	Basket 4
Pupil A	English	4	1	4
Pupil A	English Lit	3	1				3
Pupil A	Maths	5	2		5
Pupil A	Geography	6	3			6
Pupil A	Science	7	3			7
Pupil A	History	4	3				4
Pupil A	Spanish	5	3				5
Pupil A	French	6	3			6
Pupil A	Science Additional	2	3				2
Pupil A	Textiles	6	4				6
Pupil A	Drama	2	4				2

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

[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?

[Anonymous]

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