Alias for Row value

[Mathis1]

Hi everyone,

I have a little question regarding the alias for row

I have reports, with returned data. I would like to modify the alias of the returned data (see image below)
I'm not talking about fields or column names, but about rows. (VLOOKUP function in Excel)

I thought I could use the REPLACE function in a Calculation Table, but I couldn't do it

Do you have an idea how to do this?

Many thanks for your help

Have a good day

Data returned in the report	Want to appear in the report
DXP	WEBSITE
DHS	CRM
POS	STORE

[Connor_-_London · Accepted solution]

Hi @Mathis1 

If I have understood your question correctly, there are a couple of options here. The logic I understand you are trying to insert is:

• If [database_field] = “DXP”, return “Website”, else
• If [database_field] = “DHS”, return “CRM”, else
• If [database_field] = “POS”, return “Store”, else
• else return [something_else]

One Time Solution - Nested IFs Table Calculation

This is the least scalable solution and might be frustrating for you to do every time, but you can use the IF function in table calculations to write the logic:

if(${database_field} = "DHS"
   ,"CRM"

      ,if(${database_field} = "DXP"
      ,"Website"

         ,if(${database_field} = "POS"
         ,"Store"
         ,${database_field}
          )
       )
)

Scalable Solution - LookML CASE Statement

A CASE statement provides a similar output to the above Nested IF, but does the functionality in Looker’s modeling layer, so you don’t have to type out the table calculation every time.

If you are a LookML developer for your Looker instance, you can add the logic yourself in the LookML, else get in touch with your LookML developer and they can add it in.

Create a new dimension in your LookML view:

  dimension: [replacement_desired_field] {
    type: string
    case: {
      when: {
        sql: ${TABLE}."database_field" = "DXP";;
        label: "Website"
      }
      when: {
        sql: ${TABLE}."database_field" = "DHS";;
        label: "CRM"
      }
      when: {
        sql: ${TABLE}."database_field" = "POS";;
        label: "Store"
      }
      else: "Unknown"
    }
  }

For both of these solutions you’ll need to change [database_field] with the actual name of the field as it currently is.

The second solution is preferred in most cases because it means that the logic is reusable and keeps terminology consistent (for example, if everyone did the first solution some people might use “shop” rather than “store”).

Both of these solutions are only feasible if there are a limited number of values that the field could return, for example if the field can only return ‘DXP’, ‘DHS’ and ‘POS’ like in your example it would work. If there are more than 10 possible values, it’ll take a while to type it all out!

Hope that helps!

Connor | Redkite

[Dawid]

If you can’t create a dictionary table in your Data Model, I would create one in Looker using derived table.

SELECT

  “POS” AS code, “Store” AS label

UNION

  “DHS”, “CRM”

This allows you to keep it in one place and then join anywhere you may need

[Mathis1]

Hi @Connor_-_London,

Many thanks for your response, theses informations really helped me.

As I am not an Administrator,I have chosen the first function function: IF and everything works perfectly

Now I have a new question, still with the same idea, I would like to do exactly the same thing but for non-exact values

I have DHS01, DHS02, DHS03 and I would like it to return the CRM value for all of these value, is there any "CONTAINS" parameter when using the IF function or should I use the IF function one by one for each value?

Many thanks for your help

Kind regards

Data returned in the report	Want to appear on the report
DHS01	CRM
DHS02	CRM
DHS03	CRM

[Connor_-_London]

Hi @Mathis1 

I’m happy the solution helped! As you mentioned, the “CONTAINS” function is useful here:

if(contains(${database_field},"DHS") = yes
  ,"CRM"

     ,if(${database_field} = "DXP"
     ,"Website"

        ,if(${database_field} = "POS"
        ,"Store"
        ,${database_field}
         )
      )
)

I would recommend that as you are writing a lot of this logic out, if it is going to be used elsewhere or by other users, it is worth talking to your administrator / LookML developer to see if they want to add this logic into LookML for you. Happy to help on that too!