Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
franciscoacunac
New Contributor III

Pick only one of many data fields

Hello dear community, I'm having a problem with some charts that I hope you can help me with.

I have the following data about professors at a University with their grad code.

 

IDNameGradCode
123John88
123John99
123John100
124Mark100
124Mark99

Here we have John and Mark. Both of them have more than one GradCode. The codes means: 88=PhD, 99= Masters, 100= Licenciate.

They have more than one because the systems keeps a record of the grads they had since they've been hired, and during that time both John and Mark kept studying and getting more degrees.

What I need to do, is show in a chart all the professors at the University that have a PhD, a Masters and a Licenciate Degree. The trick is that for John, I only need him to appear once with his PhD Degree, and Mark only once with his Masters Degree.

I need to show every professor only with his highest degree.

Hope you can help me with this.

Happy holidays

1 Solution

Accepted Solutions
MVP
MVP

Re: Pick only one of many data fields

Create a calculated dimension

=Aggr( Min( GradCode) , ID)

then only one expression

=Count(Distinct ID)

View solution in original post

7 Replies
MVP
MVP

Re: Pick only one of many data fields

Create a straight table chart with dimensions ID and Name, then as expression

=Min(GradCode)

resp.

=Max(GradCode)

[I think the minimum value is what you need as highest grade, but not 100% sure]

Re: Pick only one of many data fields

May be this:

Dimension: Name

Expression: =Pick(Match(FirstSortedValue(GradCode, GradCode), 88, 99, 100), 'Phd', 'Masters', 'Licenciate')

Capture.PNG

Partner
Partner

Re: Pick only one of many data fields

i Have a look at the attach example

by using firstsortedvalue(Name , GradeCode) , you'll get the highest degree for each name

franciscoacunac
New Contributor III

Re: Pick only one of many data fields

But what if I wanted to do 3 expressions to show in the chart?

So far I have these expressions:

For PhD's:

(count({<GradCode={'88'} >}Distinct ID)

For Masters:

(count({<GradCode={'99'} >}Distinct ID)

For Licenciate:

(count({<GradCode={'100'} >}Distinct ID)

The problem is that John appears in all of them and I need him to appear just in the PhD bar on the chart.

MVP
MVP

Re: Pick only one of many data fields

Create a calculated dimension

=Aggr( Min( GradCode) , ID)

then only one expression

=Count(Distinct ID)

View solution in original post

franciscoacunac
New Contributor III

Re: Pick only one of many data fields

You mean on the script? or in the chart?

franciscoacunac
New Contributor III

Re: Pick only one of many data fields

I'ts working so far!