clapper | GNOME media player built using GJS with GTK4 toolkit | Media library

Good evening everyone,

I am using OBIEE and I am trying to extract a file containing some candidates' information to keep in our records, as my organization will need to delete most data soon.

I have data related to recruiting that people put in their applications for job vacancies.

I am trying to have a single row per candidate per application (i.e. if a candidate applied to 2 different jobs, it will count as 2 rows), and insert the highest education, the related insitution, their most recent job title, and the most recent employer name.

I have these facts:

ID, degree_type, institution, job title, employer.

and they all have the starting date and the graduation date.

When I extract the report, I get something like this:

ID degree_type institution job_title employer 001 Doctorate Univ. A eater google 001 Master's Univ. B sleeper samsung 001 Other Univ. A jumper apple 002 Bachelor's Univ. C clapper nutella 002 Master's Univ. D somethinger fujitsu 002 Doctorate Univ. A somethinger fujitsu 003 Other Univ. E eater EU 003 Doctorate Univ. Z spy UN

As you can see, each person might or might not have different levels of education, and when I extract this analysis, I have one ID with multiple rows, as many as every degree and every job experience, sorted by chronological order.

This creates some readability issues. Besides, we only want the highest degree and the most recent job.

So something like this.

ID degree_type institution job_title employer 001 Doctorate Univ. A eater google 002 Doctorate Univ. A somethinger fujitsu 003 Doctorate Univ. Z eater EU

Instead, when I try to apply filters or step, I can only manage to obtain a result based on either

A) the most recent degree and the most recent employer, or B) each degree and each work experience that was carried out in the same time period of the degree.

Option A does not work for multiple reasons, e.g., if someone got a certification after a PhD, I will have a person with "other" whereas they should have "doctorate"

Option B is not useful at the moment, as we only want one row. Besides, if I worked after getting a degree, that work experience would not appear as it only shows the work carried out during the studies.

I am new with OBIEE, and I am not familiar with SQL. I usually use R, and for completely different reasons.

If I could assign a value to each degree and then filter by the highest (eg., IF there is a doctorate, THEN show it and STOP. ELSE show master's. IF not master's and doctorate, THEN show bachelor's and STOP.) And then add the work experience by date, that would be great.

Is there a way to do this?

Thank you so much! And apologies if it does not make any sense.

PS> I saw this reply already How To Get Highest Education Using MySQL?

but that person has multiple columns for each degree, whereas I have them altogether.

...