How to make a value that has appeared not appear again

[Anonymous]

TABLE 1:

STUDENT_LID	TYPE	STATUS	RECEIVE_SCHOLARSHIP
1000982	SCHP	A	YES
1000982	BURS	A	NO
1000982	BURS	A	NO
1000982	BURS	A	NO
1000982	BURS	A	NO
1000982	BURS	A	NO
1000982	SCHP	A	YES
1001124	BURS	A	NO
1001124	SCHS	A	YES

Context:

For TABLE 1, I have created a column "RECEIVE_SCHOLARSHIP" , students with TYPE = 'SCHP' or 'SCHS' and STATUS = 'A'., the column will display 'YES'.

if( STATUS = 'A' and (N_FA_TYPE = 'SCHP' or N_FA_TYPE = 'SCHS'),'Yes','No') as RECEIVE_SCHOLARSHIP

Student with TYPE = 'BURS' are not awarded scholarship which RECEIVE_SCHOLARSHIP will display 'NO'.

TABLE 2(Main Table):

STUDENT_LID	PROG_STATUS	ADMIT_TERM
1000982	AD	1730
1000982	AC	1730
1000982	AC	1730
1000982	CM	1730
1000982	AD	1420
1000982	AC	1420
1000982	AC	1420
1000982	AC	1420
1000982	CM	1420
1001124	AD	1410
1001124	AC	1410
1001124	DC	1410
1001124	AD	1720
1001124	AC	1720
1001124	AC	1720

Now i left keep the Table 2(Main Table) and load the data.

The issue is that when i put this data into a PIE CHART, students that were awarded scholarship are also counted in the "students that were not awarded scholarship". This is because a student can have both SCHP and BURS at the same time, thus it will be counted in both the "awarded scholarship" and  "students that were not awarded scholarship".

What i want to be able to do is IF student has been awarded 'SCHP' and 'BURS' at the same time, only display this student as "awarded scholarship".

Thank you for reading, and please do drop a reply to help me.

[tomasz_tru]

set analysis witch e() might be handy. More here: LivingQlik Roots: The Ultimate QlikView Set Analysis Reference

[Gysbert_Wassenaar]

You could fix up Table 1 some so you get a column that show if a student really has a scholarship or not:

LEFT JOIN ([TABLE 1])

LOAD DISTINCT

     STUDENT_LID,

     'YES' AS ACTUALLY_REALLY_DOES_RECEIVE_A_SCHOLARSHIP

RESIDENT

     [TABLE 1]

WHERE

     RECIEVE_SCHOLARSHIP = 'YES'

     ;

Then you can use ACTUALLY_REALLY_DOES_RECEIVE_A_SCHOLARSHIP as dimension in your pie chart and as measure count(distinct STUDENT_LID)

[Vegar]

Consider making a mapping table and do a mapping load in the main table load.

[Anonymous]

Hi, I am currently trying your method, but I'm stuck. I created a new table to fix up table 1, however when i use the ACTUALLY_REALLY_DOES_RECEIVE_A_SCHOLARSHIP as dimension, it shows 100% 'YES'. I tried left keep and left join.

T4: //students with scholarships

LEFT KEEP(TABLE 1)

LOAD DISTINCT

     EMPLID,

     'YES' AS ACTUALLY_REALLY_DOES_RECEIVE_A_SCHOLARSHIP

RESIDENT

     [TABLE 1]

WHERE

     RECIEVEDSCHOLARSHIP = 'YES'

     ;

[Vegar]

You have two students in your data set 1000982 and 1001124. Both students would are recieving a scholarship according to your [TABLE 1]. Gysbert Wassenaars solution is showing correct numbers unless we missunderstand your issue.

Please explain.  Maybe your could show us the expected result from your dataset?

[Anonymous]

STUDENT_LID	TYPE	STATUS	RECEIVE_SCHOLARSHIP
1000982	SCHP	A	YES
1000982	BURS	A	NO
STUDENT_ID RECEIVE_SCHOLARSHIP 10009282 YES

Basically,  if the student has appeared in the RECIEVE_SCHOLARSHIP column as 'YES' AND 'NO'. I want to be able to display him as 'YES' ONLY in the charts.

Re: How to make a value that has appeared not appear again

[Vegar]

Please have a look at my attached QVW. The output will look like this