How to create a category from two fields

[akpofureenughwu]

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

[sunny_talwar]

What would be the output you are looking to get based on this data that you have provided?

[akpofureenughwu]

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

[sunny_talwar]

Can you provide expected output in a table form?

[akpofureenughwu]

Hello Sunny

Find the attached file.... look for the expected result tab

Regards

[sunny_talwar]

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 ]);

[Anonymous]

do you have 'null' or null() entries?

[akpofureenughwu]

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

[Anonymous]

does Sunny's solution work for you?

[akpofureenughwu]

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

[Anonymous]

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,

...