Pick only one of many data fields

[Anonymous]

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.

ID	Name	GradCode
123	John	88
123	John	99
123	John	100
124	Mark	100
124	Mark	99

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

[swuehl]

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]

[sunny_talwar]

May be this:

Dimension: Name

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

[lironbaram]

i Have a look at the attach example

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

[Anonymous]

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.

[swuehl · Accepted solution]

Create a calculated dimension

=Aggr( Min( GradCode) , ID)

then only one expression

=Count(Distinct ID)

[Anonymous]

You mean on the script? or in the chart?

[Anonymous]

I'ts working so far!