Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
set analysis witch e() might be handy. More here: LivingQlik Roots: The Ultimate QlikView Set Analysis Reference
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)
Consider making a mapping table and do a mapping load in the main table load.
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'
;
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?
STUDENT_LID | TYPE | STATUS | RECEIVE_SCHOLARSHIP | ||||
1000982 | SCHP | A | YES | ||||
1000982 | BURS | A | NO | ||||
| |||||||
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.
Please have a look at my attached QVW. The output will look like this