Rename columns used in a Join

[VladL]

Hello!

I would like to join to the same field with two different ids in a parent table and rename the columns instead of having them share the joined column’s name:

(action model)

   join: updated_by {
    from: users
    type: left_outer
    view_label: "Action"
    fields: [updated_by.full_name]
    sql_on: ${action.updated_by} = ${updated_by.id} ;;
    relationship: many_to_one
    }

  join: completed_by {
    from: users
    type: left_outer
    view_label: "Action"
    fields: [completed_by.full_name]
    sql_on: ${action.completed_by} = ${completed_by.id} ;;
    relationship: many_to_one
  }

(users view)

  dimension: full_name {
    type: string
    sql: CONCAT(${TABLE}.first_name, " ", ${TABLE}.last_name) ;;
  }

What I get with this is two fields in my Action explore that say “Full Name”; what I’d like to see is these fields renamed “Updated Name” and “Completed Name” (e.g. use a label with the join). It looks like I can create a lot of repeating dimensions for each of these full names but that seems like a lot of redundant LookML.

[VladL]

If I had a way to turn this SQL:

CONCAT(updated_by.first_name, " ", updated_by.last_name) AS updated_by.full_name

into this:

CONCAT(updated_by.first_name, " ", updated_by.last_name) AS updated_by.updated_name

I’d be set.

[Dawid]

How about this? Sorry I can’t test it right now so it’s just from the top of my head:

dimension: updated_by_name {
   sql: ${updated_by.full_name} ;;
}

dimension: completed_by_name {
  sql: ${completed_by.full_name} ;;
}

And you would add it to the users.view.lkml

[VladL]

Interesting -

The dimension can accept the join name; it’s nice not having to repeat the CONCAT in the users view. I have this working … thanks!

Is there a way to rename the full_name field in the join in the Action model? Then the repeated code in the users view would not be necessary.

[VladL]

@Dawid -

This approach does create reports but Developer mode is complaining about errors (one for each dimension):

Unknown view “completed_by” referenced in “users.completed_name”. View “completed_by” does not exist in model …

[VladL]

I changed:

dimension: updated_by_name {
   sql: ${updated_by.full_name} ;;
}

to:

dimension: updated_by_name {
   sql: ${full_name} ;;
}

… and that resolved the LookML error.