How to write a case statement that returns a dimension

I am trying to write a formula in a blended data set that is going to return the visitor source. The problem is since the visitor sources from the two different data sets dont exactly match, so they return null if it’s not exact match.

Case 
when Visitor Source (Table 1)="null" then Visitor Source (Table 2)
when Visitor Source (Table 2)="null" then Visitor Source (Table 1)
when Visitor Source (Table 1) does not CONTAINS_TEXT("null") then Visitor Source (Table 1)
when Visitor Source (Table 2) does not CONTAINS_TEXT("null") then Visitor Source (Table 2)
END

This was my idea to create a new dimension that rewrites the null values to the value of the source of the other column, but obviously I don’t know how to write the formula. 

0 1 439
1 REPLY 1

Hi Julian,

Based on my understanding of your question, I am going to provide below suggestion.

dimension: yes_no {
type: string
suggest_persist_for: "1 second"
sql: CASE when ${test_start_date.latest_date} is null then "NO" else "YES" END ;;
#sql: ${test_start_date.latest_date};;
}

dimension: new_filter {
type: string
suggest_persist_for: "1 second"
sql: CASE when ${yes_no} = "YES" then ${test_start_date.latest_date_str} else cast(${created_date} as string) END ;;
}

In the above example, I have “yes_no” is dimension defined on a another dimension to find whether latest_date is null or not.

Based off this “yes_no”, I defined another dimension which returns of latest_date or created_date values from table, returns as String as seen above.

This helps me to choose latest_date or other dates depend on what data I am going to pull on explore.

In your case, you wanted to read data from 2 tables, but same column[Visitor Source]?

First thing, you need to join these two tables based on applicable field, then you will be able to identify Table2 in Table1. This needs to be in model file. Please refer here

https://cloud.google.com/looker/docs/working-with-joins#joins_start_with_an_explore

Then work on writing case definition accordingly in one of view file either Table 1 or Table 2 depends how you define your join.

Let me know, if this helps.