view: label_territory_carveout {
  derived_table: {
    sql: select v.vendor_id, IF(v.company IS NULL OR v.company = '', v.name, v.company) AS vendor_name, c2.name, v.status, v.priority, v.owner, v.label_identifier, v.is_owned, vcc.id, vcc.cont_start,
      vcc.cont_end, vcc.digital_split, vcc.physical_split, vcc.notice_required_in_days, vcc.rollover_length_in_months, vcc.ringtone_publishing_type,
      vcc.physical_track_publishing_type,vcc. sync_admin_commission, vcc.royalty_collection_commission, vcc.sx_royalty_collection_commission,
      dmc.dms_master_split as soungexchange_split, dmc2.dms_master_split as performance_rights,
      max(case when sc.country_id=1 and sc.subaccount_id is null then sc.soundscan_code end) soundscan_code_us,
      max(case when sc.country_id=2 and sc.subaccount_id is null then sc.soundscan_code end)  soundscan_code_ca,
      concat(oau2.f_name, " ", oau2.l_name) as closer, concat(oau.f_name, " ", oau.l_name) as client_manager,
      group_concat(distinct c.name) as territory_carveouts, group_concat(distinct c3.name) as royalty_collection_territory
      from art_relations.vw_active_vendor_contract vw
      left join art_relations.vendor v on vw.vendor_id=v.vendor_id
      left join art_relations.soundscan_codes sc on v.vendor_id = sc.vendor_id
      left join art_relations.orchadmin_users oau ON v.assigned_to = oau.id
      left join art_relations.vendor_contract vcc on vw.vendor_contract_id=vcc.id
      left join art_relations.orchadmin_users oau2 ON   oau2.id = vcc.orchrep_name
      left join art_relations.vendor_dms_master_contract dmc on dmc.vendor_contract_id=vw.vendor_contract_id and dmc.dms_master_id = 549
      left join art_relations.vendor_dms_master_contract dmc2 on dmc2.vendor_contract_id=vw.vendor_contract_id and dmc2.dms_master_id = 682
      left join art_relations.country c on find_in_set(c.id, vcc.territory_carve_out)
      left join art_relations.country c2 on v.country=c2.id
      left join art_relations.country c3 on find_in_set(c3.id, vcc.royalty_collection_territory)
      group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
       ;;
  }

  measure: count_distinct_labels {
    view_label: "Label"
    label: "Number of Labels"
    description: "Unique Label counts (by group; within your filters)"
    hidden: no
    suggestable: no
    sql: ${TABLE}.vendor_id ;;
    type: count_distinct
  }

  dimension: label_id {
    label: "Label ID"
    view_label: "Label"
    type: number
    sql: ${TABLE}.vendor_id ;;
  }

  dimension: id {
    label: "Vendor Contract ID"
    view_label: "Label"
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: label_name {
    label: "Name"
    view_label: "Label"
    type: string
    sql: ${TABLE}.vendor_name ;;
  }

  dimension: soundscan_code_us {
    label: "Soundscan Code US"
    view_label: "Label"
    type: string
    sql: ${TABLE}.soundscan_code_us ;;
  }

  dimension: soundscan_code_ca {
    label: "Soundscan Code CA"
    view_label: "Label"
    type: string
    sql: ${TABLE}.soundscan_code_ca ;;
  }

  dimension: label_country {
    label: "Country"
    view_label: "Label"
    type: string
    sql: ${TABLE}.name ;;
  }

  dimension: label_status {
    label: "Status"
    view_label: "Label"
    type: string
    sql: ${TABLE}.status ;;
  }

  dimension: label_priority {
    label: "Priority"
    view_label: "Label"
    type: number
    sql: ${TABLE}.priority ;;
  }

  dimension: label_owner {
    label: "Owner"
    view_label: "Label"
    type: string
    sql: ${TABLE}.owner ;;
  }

  dimension: closer {
    label: "Closer"
    view_label: "Label"
    type: string
    sql: ${TABLE}.closer ;;
  }

  dimension: client_manager {
    label: "Client Manager"
    view_label: "Label"
    type: string
    sql: ${TABLE}.client_manager ;;
  }

  dimension: is_owned {
    label: "Is Owned"
    view_label: "Label"
    type: string
    sql: ${TABLE}.is_owned ;;
  }

  dimension: label_identifier {
    label: "Type"
    view_label: "Label"
    type: string
    sql: ${TABLE}.label_identifier ;;
  }

  dimension: label_cont_start {
    label: "Cont Start"
    view_label: "Label"
    type: date
    sql: ${TABLE}.cont_start ;;
  }

  dimension: label_cont_end {
    label: "Cont End"
    view_label: "Label"
    type: date
    sql: ${TABLE}.cont_end ;;
  }

  dimension: label_digital_split {
    label: "Digital Split"
    view_label: "Label"
    type: number
    sql: ${TABLE}.digital_split ;;
    value_format: "0.00%"
  }

  dimension: label_physical_split {
    label: "Physical Split"
    view_label: "Label"
    type: number
    sql: ${TABLE}.physical_split ;;
    value_format: "0.00%"
  }

  dimension: ringtone_publishing_type {
    view_label: "Label"
    type: string
    sql: ${TABLE}.ringtone_publishing_type ;;
  }

  dimension: physical_track_publishing_type {
    view_label: "Label"
    type: string
    sql: ${TABLE}.physical_track_publishing_type ;;
  }

  dimension: notice_required_in_days {
    label: "Notice Required in Days"
    view_label: "Label"
    type: number
    sql: ${TABLE}.notice_required_in_days ;;
  }

  dimension: rollover_length_in_months {
    label: "Rollover Length in Months"
    view_label: "Label"
    type: number
    sql: ${TABLE}.rollover_length_in_months ;;
  }

  dimension: sync_admin_commission {
    label: "Sync Admin Fee"
    view_label: "Label"
    type: number
    sql: ${TABLE}.sync_admin_commission ;;
    value_format: "0.00%"
  }

  dimension: royalty_collection_commission {
    label: "Royalty Collection Commision"
    view_label: "Label"
    type: number
    sql: ${TABLE}.royalty_collection_commission ;;
    value_format: "0.00%"
  }

  dimension: royalty_collection_territory {
    label: "Royalty Collection Territory"
    view_label: "Label"
    type: string
    sql: ${TABLE}.royalty_collection_territory ;;
  }

  dimension: sx_royalty_collection_commission {
    label: "SX Royalty Collection Commission"
    view_label: "Label"
    type: number
    sql: ${TABLE}.sx_royalty_collection_commission ;;
    value_format: "0.00%"
  }

  dimension: soungexchange_split {
    label: "Sound Exchange Split (DMS Master)"
    view_label: "Label"
    type: number
    sql: ${TABLE}.soungexchange_split ;;
    value_format: "0.00%"
  }

  dimension: performance_rights {
    label: "Performance Rights Split (DMS Master)"
    view_label: "Label"
    type: number
    sql: ${TABLE}.performance_rights ;;
    value_format: "0.00%"
  }

  dimension: label_territory_carveouts {
    label: "Territory Carveouts"
    view_label: "Label"
    type: string
    sql: ${TABLE}.territory_carveouts ;;
  }

#   set: detail {
#     fields: [
#       vendor_id,
#       company,
#       status,
#       priority,
#       owner,
#       label_identifier,
#       cont_start,
#       cont_end,
#       digital_split,
#       notice_required_in_days,
#       rollover_length_in_months,
#       group_concatc_name
#     ]
#   }
}