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]

This isn't working?

[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],

    ApplyMap('UniversityTableMap',iUniversityId,Null())&' - ' & ApplyMap('SchoolTableMap',iSchoolId,Null()) as [Univeristy - School Name];

SQL SELECT iBeneficiaryId,

    vSurname,

    vMiddleName,

    vFirstName,

    iUniversityId,

    iBankId,

    iCourseofStudyId,

    dDOB,

    iTitleId,

    vAccountNumber,

    iSchoolId,

    dtCourseStartDate,

    dtCourseEndDate

FROM xxxxBI.crd.BeneficiaryInfo;

[akpofureenughwu]

Hello Sunny..

It didn't give me the desired result...

In the attached picture, when I implemented your suggestion... I got the display in the picture

What I really want to achieve is:  [Univeristy - School Name]; will have two groups.. University (where University of Abuja, Univeristy of Benin will fall into while Itohan school will belog to school group)

I think this is more like Hierarchy.

Thanks

[sunny_talwar]

Attached picture? Don't see anything attached

[akpofureenughwu]

Sorry... Here it is

[sunny_talwar]

So the row which has University, doesn't have School and the one that has school doesn't have university? How would you combine them? I am confused? How are School and Universities associated with each other?

[akpofureenughwu]

Firstly sorry for the confusion..

On the database design, there is a student table that has university id (a primary key in the university table) and school id (primary key in school table).

Is it possible that at  front end, the users can classify the users into two groups: Students in university and students in school?

[akpofureenughwu]

bill.markham‌ roharoha‌

Please what's your suggestion?

[Anonymous]

Hi,

you should specify your needs a bit clearer.

Why don't you just build a small (Excel / Inline) example and explain in small steps what you need.

For me it is confusing because I neither have your data nor really understand what result you're expecting.

[akpofureenughwu]

roharoha‌ bill.markham‌

Attached to this message are the representation of the data strcture of the table discussed yesterday as well as the content of the table.

The designed design is on the dashboard, there will be a filter pane called scholarship category... when you hit the button, the user will see two groups... Secondary category contains all the schools in school table and tertiary category which contain universities

Hope this meets you well

Thank you sir