Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'.
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.
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 |
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 |
Ni hao! my friend i have a question for you with this thing ¿can you extract the data with a DISTINCT function to group the STUDENT_LID? if you can do this, the problem will be solved!
I wait for your answer.
Greetings!!
Ni hao! Thanks for your reply. The data cannot be extracted with a Distinct function to group the STUDENT_ID, because the it wont be able to detect if the students have received scholarship or not. Do you have any idea in mind?
I look forward to your answer.
Ni hao! well i don´t know too much about Qlick Sense but if you select just the student_lid had the conditions "SCHP" or "SCHS" to show in the PIE CHART. with an if condition a count it, and i have a question:¿why do you have many times repeadly a student_lid in the table? The logic says you most update the field TYPE to every student_lid has the condition receive a scholarship
Greetings!