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
Super, please mark one of the response as correct to close this thread.Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny
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;
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
Attached picture? Don't see anything attached
Sorry... Here it is
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?
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?
Please what's your suggestion?
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.
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