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 | |
| 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 | |
| 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.
I am assuming that OBIEE is just a DB and you can use SQL to get the info.
I also assume that the ID column you provide represents unique ID per Employee.
Your task requires intermediate if not advanced SQL techniques to solve. Here are the steps.
you need to codify the sort order of the degree level - in 3NF (third normal form) you would add a reference table to store one row per degree and include degree_name varchar column (primary key) to equal the values you list in your post, but then another column degree_sort integer that sorts the degrees the way you want. You would join to this table on the varchar value and return the degree_sort value
Handling ties: Another complexity is how to handle the possibility of a employee having multiple jobs at the doctorate (I presume that is the highest) education level - you would need a "start_date" or some data point to break ties.
Here's a stack post that explains an analogous scenario, getting the record that represents the latest revision of a document (revision is your degree level, document is your employee ID): https://stackoverflow.com/a/38854846/1279373
Your partition clause would be:
PARTITION BY id ORDER BY degree_sort DESC, start_date DESC
Note: The where clause (see sql in the referred to answer) handles "return only the rows with rank 1"; use ASC (ascending) and DESC (descending) in the ORDER BY clause to rank "low to high" or "high to low".