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.
