topic Re: How to create a category from two fields in QlikView

How to create a category from two fields

akpofureenughwu — Mon, 19 Jun 2017 13:50:22 GMT

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

Re: How to create a category from two fields

sunny_talwar — Mon, 19 Jun 2017 15:26:31 GMT

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;

Re: How to create a category from two fields

akpofureenughwu — Mon, 19 Jun 2017 16:04:26 GMT

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

Re: How to create a category from two fields

sunny_talwar — Mon, 19 Jun 2017 16:05:47 GMT

Attached picture? Don't see anything attached

Re: How to create a category from two fields

akpofureenughwu — Mon, 19 Jun 2017 16:10:36 GMT

Sorry... Here it is

Re: How to create a category from two fields

sunny_talwar — Mon, 19 Jun 2017 16:20:48 GMT

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?

Re: How to create a category from two fields

akpofureenughwu — Mon, 19 Jun 2017 16:33:20 GMT

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?

Re: How to create a category from two fields

akpofureenughwu — Mon, 19 Jun 2017 18:42:10 GMT

bill.markham‌ roharoha‌

Please what's your suggestion?

Re: How to create a category from two fields

Anonymous — Mon, 19 Jun 2017 19:02:26 GMT

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.

Re: How to create a category from two fields

akpofureenughwu — Tue, 20 Jun 2017 10:01:43 GMT

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

Re: How to create a category from two fields

sunny_talwar — Tue, 20 Jun 2017 14:27:33 GMT

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

Re: How to create a category from two fields

akpofureenughwu — Tue, 20 Jun 2017 14:46:00 GMT

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

Re: How to create a category from two fields

sunny_talwar — Tue, 20 Jun 2017 14:55:57 GMT

Can you provide expected output in a table form?

Re: How to create a category from two fields

akpofureenughwu — Tue, 20 Jun 2017 15:04:03 GMT

Hello Sunny

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

Regards

Re: How to create a category from two fields

sunny_talwar — Tue, 20 Jun 2017 16:36:04 GMT

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

Re: How to create a category from two fields

Anonymous — Tue, 20 Jun 2017 16:43:08 GMT

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

Re: How to create a category from two fields

akpofureenughwu — Tue, 20 Jun 2017 18:08:11 GMT

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

Re: How to create a category from two fields

Anonymous — Tue, 20 Jun 2017 18:24:29 GMT

does Sunny's solution work for you?

Re: How to create a category from two fields

akpofureenughwu — Tue, 20 Jun 2017 18:33:02 GMT

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

Re: How to create a category from two fields

Anonymous — Tue, 20 Jun 2017 18:44:11 GMT

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,

...