Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Thank you for reading my post,
I have a set of data in this format..
SchoolTableMap:
Mapping LOAD //Apply Map to Beneficiary info Table
iSchoolId,
vDescription as [School Name] ;
SQL SELECT iSchoolId,
vDescription,
iScholarshipCategoryid
FROM xxxx.crd.SchoolTable;
UniversityTableMap:
Mapping LOAD //Apply Map to Beneficiary info Table
iUniversityId,
vDescription as [University ];
SQL SELECT iScholarshipCategoryid,
vDescription,
iUniversityId
FROM xxxx.crd.UniversityTable;
[BeneficiaryInfo]:
LOAD iBeneficiaryId,
vSurname,
vMiddleName,
vFirstName,
vSurname&' '&vMiddleName& ' '&vFirstName as Full_Name ,
iUniversityId,
iBankId,
iCourseofStudyId,
dDOB,
iTitleId,
vAccountNumber,
iSchoolId,
dtCourseStartDate,
dtCourseEndDate,
ApplyMap('CourseMap',iCourseofStudyId,Null()) as [Course Name],
ApplyMap('SchoolTableMap',iSchoolId,Null()) as [School Name],
ApplyMap('UniversityTableMap',iUniversityId,Null()) as [Univeristy Name],
ApplyMap('BankMap',iBankId,Null()) as [Bank Name],
ApplyMap('TitleMap',iTitleId,Null()) as [Title Name];
SQL SELECT iBeneficiaryId,
vSurname,
vMiddleName,
vFirstName,
iUniversityId,
iBankId,
iCourseofStudyId,
dDOB,
iTitleId,
vAccountNumber,
iSchoolId,
dtCourseStartDate,
dtCourseEndDate
FROM xxxxBI.crd.BeneficiaryInfo;
I would love to create a field called Institution category ( a combination of University Name and School Name ) .
With this field , I can develop a chart of institution category vs amount paid... and the user can drill down from institution category to university...
I tried concatenate those fields but I didn't get the right result.
Thank you
Regards
What would be the output you are looking to get based on this data that you have provided?
From the dashboard, all the students will be grouped into two categories: Tertiary (those who are in the university) and secondary(those who are in schools).
The user can filter the students from the student table based on this filter (student category)
Regards
Can you provide expected output in a table form?
Hello Sunny
Find the attached file.... look for the expected result tab
Regards
This?
Fact:
LOAD iStudentId,
vStudentName,
iUniversityId,
iSchoolId,
If(iSchoolId = 'Null' and iUniversityId <> 'Null', 'Tertiary', If(iSchoolId <> 'Null' and iUniversityId = 'Null', 'Secondary')) as Category
FROM
[..\..\Downloads\university,school, student table info (1).xlsx]
(ooxml, embedded labels, table is [student table ]);
do you have 'null' or null() entries?
There is only one null entry for each student (either school Id or University Id)
NB: Every student MUST have a schoolId or universityId
Regards
does Sunny's solution work for you?
I'm trying to implement his suggestions on my script
I'm using the apply map function to link the student and university id to the student table
See attached for clarity
I'm still not sure, if you have 'null' or null() values...
perhaps that works alternatively:
...
If(isnull(iSchoolId) and not isnull(iUniversityId) , 'Tertiary', If(not isnull(iSchoolId) and isnull(iUniversityId), 'Secondary')) as Category,
...