looker explore dimension syntex

[shetu]

how will I convert this sql into explore script to create dimension in looker?

[Note : here table_recommendation  , Markup and Markup are column names in Bigquery]

SELECT  

 CASE WHEN table_recommendation = 'MD' THEN  Markup

         WHEN table_recommendation = 'MU' THEN  Markdown

         ELSE 0 END AS segment,

I was trying below script to create dimension but i am getting error:

dimension: Item_Count {
type: number
case: {
when: {
sql: ${TABLE}.table_recommendation = 'MD' then ${ap_markdown_9} ;;

}
when: {
sql: ${TABLE}.table_recommendation = 'MU' then ${ap_markup_9} ;;

}

else:  0
}
}

[a_shah · Accepted solution]

Looker does not have a direct parameter to enable the case statements, however we can always make use of SQL parameter in dumenisons in such scenarios. Please see below:

dimension: country {
type: string
sql: CASE WHEN ${TABLE}.COUNTRY = 'UK' THEN 'United Kingdom'
WHEN ${TABLE}.COUNTRY = 'UAE' THEN 'United Arab Emirates'
ELSE ${TABLE}.COUNTRY END ;;
}

Also the type in your case will be string and not number.

Hope this helps.

~Ashish 

[shetu]

 IT works for me .Thanks for the solution:)