Goolge Analyticsコネクターデータで日本地図を使う

Google Analyticsコネクターで取得したデータの”Region”の値には、Prefectureが付加されているので、以下のようなcase文でPrefectureを外して、Domoの日本地図で読み取れるように変換する必要があります。

 

case
when `Region` = 'Aichi Prefecture' then 'Aichi'
when `Region` = 'Akita Prefecture' then 'Akita'
when `Region` = 'Aomori Prefecture' then 'Aomori'
when `Region` = 'Chiba Prefecture' then 'Chiba'
when `Region` = 'Ehime Prefecture' then 'Ehime'
when `Region` = 'Fukui Prefecture' then 'Fukui'
when `Region` = 'Fukuoka Prefecture' then 'Fukuoka'
when `Region` = 'Fukushima Prefecture' then 'Fukushima'
when `Region` = 'Gifu Prefecture' then 'Gifu'
when `Region` = 'Gunma Prefecture' then 'Gunma'
when `Region` = 'Hiroshima Prefecture' then 'Hiroshima'
when `Region` = 'Hokkaido' then 'Hokkaido'
when `Region` = 'Hyogo Prefecture' then 'Hyogo'
when `Region` = 'Ibaraki Prefecture' then 'Ibaraki'
when `Region` = 'Ishikawa Prefecture' then 'Ishikawa'
when `Region` = 'Iwate Prefecture' then 'Iwate'
when `Region` = 'Kagawa Prefecture' then 'Kagawa'
when `Region` = 'Kagoshima Prefecture' then 'Kagoshima'
when `Region` = 'Kanagawa Prefecture' then 'Kanagawa'
when `Region` = 'Kochi Prefecture' then 'Kochi'
when `Region` = 'Kumamoto Prefecture' then 'Kumamoto'
when `Region` = 'Kyoto Prefecture' then 'Kyoto'
when `Region` = 'Mie Prefecture' then 'Mie'
when `Region` = 'Miyagi Prefecture' then 'Miyagi'
when `Region` = 'Miyazaki Prefecture' then 'Miyazaki'
when `Region` = 'Nagano Prefecture' then 'Nagano'
when `Region` = 'Nagasaki Prefecture' then 'Nagasaki'
when `Region` = 'Nara Prefecture' then 'Nara'
when `Region` = 'Niigata Prefecture' then 'Niigata'
when `Region` = 'Oita Prefecture' then 'Oita'
when `Region` = 'Okayama Prefecture' then 'Okayama'
when `Region` = 'Okinawa Prefecture' then 'Okinawa'
when `Region` = 'Osaka Prefecture' then 'Osaka'
when `Region` = 'Saga Prefecture' then 'Saga'
when `Region` = 'Saitama Prefecture' then 'Saitama'
when `Region` = 'Shiga Prefecture' then 'Shiga'
when `Region` = 'Shimane Prefecture' then 'Shimane'
when `Region` = 'Shizuoka Prefecture' then 'Shizuoka'
when `Region` = 'Tochigi Prefecture' then 'Tochigi'
when `Region` = 'Tokushima Prefecture' then 'Tokushima'
when `Region` = 'Tokyo' then 'Tokyo'
when `Region` = 'Tottori Prefecture' then 'Tottori'
when `Region` = 'Toyama Prefecture' then 'Toyama'
when `Region` = 'Wakayama Prefecture' then 'Wakayama'
when `Region` = 'Yamagata Prefecture' then 'Yamagata'
when `Region` = 'Yamaguchi Prefecture' then 'Yamaguchi'
when `Region` = 'Yamanashi Prefecture' then 'Yamanashi'
else 'Other' end

 

Comments

  • 一行でも書けました。

    replace(`Region`,' Prefecture','')