Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

Highlighted
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!