Hi team, I have a table called

company

, and each entry in

company

has some number of

employee

. I am creating a table that shows the most recently hired employee based on start date (which is attached to the employees table) for each company. How can I do this? Metrics only allow for numerical values to be shown, but I want to show: • Company Name • Employee Name • Employee Start Date

Hello Kraig, the MAX function returns the maximun value of all numbers in the set, e.g.,

SELECT MAX(fact)

More details can be found here: MAX | GoodData Cloud. Please give this a try and let us know if it helps.

Hey @Moises Morales I tried using the MAX function but because it returns a numerical value not a string (it also works for strings but metrics won't appear if they are a string) I'm not sure how to make it show up in a table

In this case, I recommend using the case statement alongside conditional formatting to display a string instead of the numerical value, e.g.: SELECT CASE WHEN condition1 THEN outcome1, WHEN condition2 THEN outcome 2 ELSE outcome3 END Numerical formatting: [=1] String

Hi Kraig, in GoodData a metric always returns a number and I am afraid you will need to have some metric in your visualization to apply this kind of logic therefore an additional column. However you can use the metric formatting to at least somehow “disguise” this number (i.e. format it as a space). Regarding the metric itself to get the last hired employee, I believe something like this might work:

SELECT COUNT({label/employee_id}) 
WHERE

(SELECT ROW_NUMBER(
  (SELECT DATETIME_DIFF("2020-01-01",{label/hire_date.day}, DAY) BY {dataset/hires})
  ) DESC WITHIN ({label/company_id}) ) = 1

The row_number =1 condition ensures that you will always get only one record per company. Unfortunately this function does not work directly with dates, only numbers, so we are using a datetime_diff sub-metric here to convert date to a number of days since a fixed date. If I understood your data model correctly, if you use this metric in a visualization table with Company, Employee and Hire Date, it should give you for each company the last hired employee. I hope this helps.

@Michal Hauzírek thanks for communicating that. Yeah I had no problems with the actual metric, it's just returning a non-numeric value I needed to do. Thanks for the help!