Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III
Creator III

How to create a category from two fields

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

1 Solution

Accepted Solutions
sunny_talwar

Super, please mark one of the response as correct to close this thread.Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

View solution in original post

23 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

bill.markhamroharoha

Please what's your suggestion?

Anonymous
Not applicable

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
Creator III
Creator III
Author

roharohabill.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