data.ml
LKML Insurance brokerage

Claim Line

Claim Line model for insurance brokerage.

inferred

Blocks

claim_line.view.lkml · 476 lines
inferred 
view: claim_line {
  sql_table_name: ClaimLine ;;

  dimension: id {
    primary_key: yes
    sql: ${TABLE}.ID ;;
  }

  dimension: account_code {
    sql: ${TABLE}.AccountCode ;;
  }

  dimension: admission_id {
    sql: ${TABLE}.AdmissionId ;;
  }

  dimension: admission_length_of_stay {
    type: int
    sql: ${TABLE}.AdmissionLengthOfStay ;;
  }

  dimension: asccode {
    sql: ${TABLE}.ASCCode ;;
  }

  dimension: bed_type_code {
    sql: ${TABLE}.BedTypeCode ;;
  }

  dimension: benefit_code {
    hidden: yes
    sql: ${TABLE}.BenefitCode ;;
  }

  dimension: benefit_modifier_code {
    sql: ${TABLE}.BenefitModifierCode ;;
  }

  dimension: bill_type_frequency {
    sql: ${TABLE}.BillTypeFrequency ;;
  }

  dimension: check_no {
    sql: ${TABLE}.CheckNo ;;
  }

  dimension: claim_line_no {
    sql: ${TABLE}.ClaimLineNo ;;
  }

  dimension: claim_no {
    sql: ${TABLE}.ClaimNo ;;
  }

  dimension: claim_status {
    sql: ${TABLE}.ClaimStatus ;;
  }

  dimension: co_insurance {
    sql: ${TABLE}.CoInsurance ;;
  }

  dimension: co_insurance_pct {
    type: int
    sql: ${TABLE}.CoInsurancePct ;;
  }

  dimension: co_pay {
    sql: ${TABLE}.CoPay ;;
  }

  dimension: cobamount {
    sql: ${TABLE}.COBAmount ;;
  }

  dimension: company_code {
    sql: ${TABLE}.CompanyCode ;;
  }

  dimension: company_paid {
    type: number
    sql: ${TABLE}.CompanyPaid ;;
  }

  dimension: days_supply {
    type: int
    sql: ${TABLE}.DaysSupply ;;
  }

  dimension: deductible {
    sql: ${TABLE}.Deductible ;;
  }

  dimension: department_code {
    sql: ${TABLE}.DepartmentCode ;;
  }

  dimension: diagnosis_code {
    sql: ${TABLE}.DiagnosisCode ;;
  }

  dimension: discounted_amount {
    sql: ${TABLE}.DiscountedAmount ;;
  }

  dimension: discounted_charge {
    sql: ${TABLE}.DiscountedCharge ;;
  }

  dimension: dispensed_quantity {
    type: int
    sql: ${TABLE}.DispensedQuantity ;;
  }

  dimension: dispensing_fee {
    sql: ${TABLE}.DispensingFee ;;
  }

  dimension: drgcode {
    sql: ${TABLE}.DRGCode ;;
  }

  dimension: drug_tier {
    sql: ${TABLE}.DrugTier ;;
  }

  dimension: drug_type {
    sql: ${TABLE}.DrugType ;;
  }

  dimension: due_provider {
    sql: ${TABLE}.DueProvider ;;
  }

  dimension: employee_paid {
    sql: ${TABLE}.EmployeePaid ;;
  }

  dimension: episode_number {
    sql: ${TABLE}.EpisodeNumber ;;
  }

  dimension: facility_type_code {
    sql: ${TABLE}.FacilityTypeCode ;;
  }

  dimension: family_coverage_code {
    sql: ${TABLE}.FamilyCoverageCode ;;
  }

  dimension: family_id {
    sql: ${TABLE}.FamilyId ;;
  }

  dimension: group_code {
    sql: ${TABLE}.GroupCode ;;
  }

  dimension: icd9_diagnosis1 {
    sql: ${TABLE}.ICD9Diagnosis1 ;;
  }

  dimension: icd9_diagnosis2 {
    sql: ${TABLE}.ICD9Diagnosis2 ;;
  }

  dimension: icd9_diagnosis3 {
    sql: ${TABLE}.ICD9Diagnosis3 ;;
  }

  dimension: icd9_diagnosis4 {
    sql: ${TABLE}.ICD9Diagnosis4 ;;
  }

  dimension: include_for_symmetry {
    sql: ${TABLE}.IncludeForSymmetry ;;
  }

  dimension: ineligible_charge {
    sql: ${TABLE}.IneligibleCharge ;;
  }

  dimension: ineligible_reason {
    sql: ${TABLE}.IneligibleReason ;;
  }

  dimension: ingredient_cost {
    sql: ${TABLE}.IngredientCost ;;
  }

  dimension: inpatient_stay_indicator {
    sql: ${TABLE}.InpatientStayIndicator ;;
  }

  dimension: is_discounted {
    type: int
    sql: ${TABLE}.IsDiscounted ;;
  }

  dimension: is_dispense_as_written {
    sql: ${TABLE}.IsDispenseAsWritten ;;
  }

  dimension: is_formulary {
    sql: ${TABLE}.IsFormulary ;;
  }

  dimension: is_generic {
    sql: ${TABLE}.IsGeneric ;;
  }

  dimension: is_in_network {
    type: int
    sql: ${TABLE}.IsInNetwork ;;
  }

  dimension: is_mail_order {
    sql: ${TABLE}.IsMailOrder ;;
  }

  dimension: is_office_visit {
    type: int
    sql: ${TABLE}.IsOfficeVisit ;;
  }

  dimension: is_prior_authorization {
    sql: ${TABLE}.IsPriorAuthorization ;;
  }

  dimension: is_processed {
    sql: ${TABLE}.IsProcessed ;;
  }

  dimension: is_refill {
    sql: ${TABLE}.IsRefill ;;
  }

  dimension: is_rx {
    type: int
    sql: ${TABLE}.IsRx ;;
  }

  dimension: is_wellness {
    type: int
    sql: ${TABLE}.IsWellness ;;
  }

  dimension: location_code {
    sql: ${TABLE}.LocationCode ;;
  }

  dimension: medicare_amount {
    sql: ${TABLE}.MedicareAmount ;;
  }

  dimension: member_age {
    type: int
    sql: ${TABLE}.MemberAge ;;
  }

  dimension: member_coverage_code {
    sql: ${TABLE}.MemberCoverageCode ;;
  }

  dimension: member_id {
    sql: ${TABLE}.MemberId ;;
  }

  dimension: member_status {
    sql: ${TABLE}.MemberStatus ;;
  }

  dimension: metric_quantity {
    type: int
    sql: ${TABLE}.MetricQuantity ;;
  }

  dimension: metric_unit {
    sql: ${TABLE}.MetricUnit ;;
  }

  dimension: ndc {
    sql: ${TABLE}.NDC ;;
  }

  dimension: network_code {
    sql: ${TABLE}.NetworkCode ;;
  }

  dimension: npi {
    sql: ${TABLE}.NPI ;;
  }

  dimension: ordering_provider_code {
    sql: ${TABLE}.OrderingProviderCode ;;
  }

  dimension: original_charge {
    sql: ${TABLE}.OriginalCharge ;;
  }

  dimension: other_paid1 {
    sql: ${TABLE}.OtherPaid1 ;;
  }

  dimension: other_paid2 {
    sql: ${TABLE}.OtherPaid2 ;;
  }

  dimension: other_paid3 {
    sql: ${TABLE}.OtherPaid3 ;;
  }

  dimension: patient_status {
    sql: ${TABLE}.PatientStatus ;;
  }

  dimension: pharmacy_provider_code {
    sql: ${TABLE}.PharmacyProviderCode ;;
  }

  dimension: pharmacy_provider_name {
    sql: ${TABLE}.PharmacyProviderName ;;
  }

  dimension: pharmacy_type {
    sql: ${TABLE}.PharmacyType ;;
  }

  dimension: place_of_service {
    sql: ${TABLE}.PlaceOfService ;;
  }

  dimension: plan_code {
    sql: ${TABLE}.PlanCode ;;
  }

  dimension: plan_year {
    type: int
    sql: ${TABLE}.PlanYear ;;
  }

  dimension: prescribing_provider_code {
    sql: ${TABLE}.PrescribingProviderCode ;;
  }

  dimension: prescribing_provider_name {
    sql: ${TABLE}.PrescribingProviderName ;;
  }

  dimension: procedure_code {
    sql: ${TABLE}.ProcedureCode ;;
  }

  dimension: procedure_modifier {
    sql: ${TABLE}.ProcedureModifier ;;
  }

  dimension: provider_code {
    sql: ${TABLE}.ProviderCode ;;
  }

  dimension: provider_name {
    sql: ${TABLE}.ProviderName ;;
  }

  dimension: provider_tax_id {
    sql: ${TABLE}.ProviderTaxId ;;
  }

  dimension: provider_type {
    sql: ${TABLE}.ProviderType ;;
  }

  dimension: quantity_of_services {
    type: int
    sql: ${TABLE}.QuantityOfServices ;;
  }

  dimension: revcode {
    sql: ${TABLE}.REVCode ;;
  }

  dimension: sales_tax {
    sql: ${TABLE}.SalesTax ;;
  }

  dimension: specialty_code {
    sql: ${TABLE}.SpecialtyCode ;;
  }

  dimension: underwriter {
    sql: ${TABLE}.Underwriter ;;
  }

  dimension: zipcode {
    sql: ${TABLE}.ZIPCode ;;
  }

  dimension_group: admission {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.AdmissionDate ;;
  }

  dimension_group: date_filled {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.DateFilled ;;
  }

  dimension_group: date_incurred_from {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.DateIncurredFrom ;;
  }

  dimension_group: date_incurred_to {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.DateIncurredTo ;;
  }

  dimension_group: date_paid {
    type: time
    timeframes: [time, date, week, month, year, dow]
    sql: ${TABLE}.DatePaid ;;
  }

  dimension_group: date_tag {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.DateTag ;;
  }

  dimension_group: date_written {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.DateWritten ;;
  }

  dimension_group: discharge {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.DischargeDate ;;
  }

  measure: count {
    type: count
  }

  measure: number_of_claims {
    type: count_distinct
    sql: ${claim_no} ;;
  }

  measure: plan_paid {
    type: sum
    sql: ${company_paid} ;;
  }

  measure: unique_claimants {
    type: count_distinct
    sql: ${member_id} ;;
  }

  measure: claims_per_claimants {
    type: number
    sql: ${number_of_claims} / ${unique_claimants} ;;
  }

  measure: paid_per_claimant {
    type: number
    sql: ${plan_paid} / ${unique_claimants} ;;
  }
}
claim_line.view.lookml · 292 lines
source-preserved 
- view: claim_line
  sql_table_name: ClaimLine
  fields:
  - dimension: id
    primary_key: true
    sql: ${TABLE}.ID
  - dimension: account_code
    sql: ${TABLE}.AccountCode
  - dimension: admission_id
    sql: ${TABLE}.AdmissionId
  - dimension: admission_length_of_stay
    type: int
    sql: ${TABLE}.AdmissionLengthOfStay
  - dimension: asccode
    sql: ${TABLE}.ASCCode
  - dimension: bed_type_code
    sql: ${TABLE}.BedTypeCode
  - dimension: benefit_code
    hidden: true
    sql: ${TABLE}.BenefitCode
  - dimension: benefit_modifier_code
    sql: ${TABLE}.BenefitModifierCode
  - dimension: bill_type_frequency
    sql: ${TABLE}.BillTypeFrequency
  - dimension: check_no
    sql: ${TABLE}.CheckNo
  - dimension: claim_line_no
    sql: ${TABLE}.ClaimLineNo
  - dimension: claim_no
    sql: ${TABLE}.ClaimNo
  - dimension: claim_status
    sql: ${TABLE}.ClaimStatus
  - dimension: co_insurance
    sql: ${TABLE}.CoInsurance
  - dimension: co_insurance_pct
    type: int
    sql: ${TABLE}.CoInsurancePct
  - dimension: co_pay
    sql: ${TABLE}.CoPay
  - dimension: cobamount
    sql: ${TABLE}.COBAmount
  - dimension: company_code
    sql: ${TABLE}.CompanyCode
  - dimension: company_paid
    type: number
    sql: ${TABLE}.CompanyPaid
  - dimension: days_supply
    type: int
    sql: ${TABLE}.DaysSupply
  - dimension: deductible
    sql: ${TABLE}.Deductible
  - dimension: department_code
    sql: ${TABLE}.DepartmentCode
  - dimension: diagnosis_code
    sql: ${TABLE}.DiagnosisCode
  - dimension: discounted_amount
    sql: ${TABLE}.DiscountedAmount
  - dimension: discounted_charge
    sql: ${TABLE}.DiscountedCharge
  - dimension: dispensed_quantity
    type: int
    sql: ${TABLE}.DispensedQuantity
  - dimension: dispensing_fee
    sql: ${TABLE}.DispensingFee
  - dimension: drgcode
    sql: ${TABLE}.DRGCode
  - dimension: drug_tier
    sql: ${TABLE}.DrugTier
  - dimension: drug_type
    sql: ${TABLE}.DrugType
  - dimension: due_provider
    sql: ${TABLE}.DueProvider
  - dimension: employee_paid
    sql: ${TABLE}.EmployeePaid
  - dimension: episode_number
    sql: ${TABLE}.EpisodeNumber
  - dimension: facility_type_code
    sql: ${TABLE}.FacilityTypeCode
  - dimension: family_coverage_code
    sql: ${TABLE}.FamilyCoverageCode
  - dimension: family_id
    sql: ${TABLE}.FamilyId
  - dimension: group_code
    sql: ${TABLE}.GroupCode
  - dimension: icd9_diagnosis1
    sql: ${TABLE}.ICD9Diagnosis1
  - dimension: icd9_diagnosis2
    sql: ${TABLE}.ICD9Diagnosis2
  - dimension: icd9_diagnosis3
    sql: ${TABLE}.ICD9Diagnosis3
  - dimension: icd9_diagnosis4
    sql: ${TABLE}.ICD9Diagnosis4
  - dimension: include_for_symmetry
    sql: ${TABLE}.IncludeForSymmetry
  - dimension: ineligible_charge
    sql: ${TABLE}.IneligibleCharge
  - dimension: ineligible_reason
    sql: ${TABLE}.IneligibleReason
  - dimension: ingredient_cost
    sql: ${TABLE}.IngredientCost
  - dimension: inpatient_stay_indicator
    sql: ${TABLE}.InpatientStayIndicator
  - dimension: is_discounted
    type: int
    sql: ${TABLE}.IsDiscounted
  - dimension: is_dispense_as_written
    sql: ${TABLE}.IsDispenseAsWritten
  - dimension: is_formulary
    sql: ${TABLE}.IsFormulary
  - dimension: is_generic
    sql: ${TABLE}.IsGeneric
  - dimension: is_in_network
    type: int
    sql: ${TABLE}.IsInNetwork
  - dimension: is_mail_order
    sql: ${TABLE}.IsMailOrder
  - dimension: is_office_visit
    type: int
    sql: ${TABLE}.IsOfficeVisit
  - dimension: is_prior_authorization
    sql: ${TABLE}.IsPriorAuthorization
  - dimension: is_processed
    sql: ${TABLE}.IsProcessed
  - dimension: is_refill
    sql: ${TABLE}.IsRefill
  - dimension: is_rx
    type: int
    sql: ${TABLE}.IsRx
  - dimension: is_wellness
    type: int
    sql: ${TABLE}.IsWellness
  - dimension: location_code
    sql: ${TABLE}.LocationCode
  - dimension: medicare_amount
    sql: ${TABLE}.MedicareAmount
  - dimension: member_age
    type: int
    sql: ${TABLE}.MemberAge
  - dimension: member_coverage_code
    sql: ${TABLE}.MemberCoverageCode
  - dimension: member_id
    sql: ${TABLE}.MemberId
  - dimension: member_status
    sql: ${TABLE}.MemberStatus
  - dimension: metric_quantity
    type: int
    sql: ${TABLE}.MetricQuantity
  - dimension: metric_unit
    sql: ${TABLE}.MetricUnit
  - dimension: ndc
    sql: ${TABLE}.NDC
  - dimension: network_code
    sql: ${TABLE}.NetworkCode
  - dimension: npi
    sql: ${TABLE}.NPI
  - dimension: ordering_provider_code
    sql: ${TABLE}.OrderingProviderCode
  - dimension: original_charge
    sql: ${TABLE}.OriginalCharge
  - dimension: other_paid1
    sql: ${TABLE}.OtherPaid1
  - dimension: other_paid2
    sql: ${TABLE}.OtherPaid2
  - dimension: other_paid3
    sql: ${TABLE}.OtherPaid3
  - dimension: patient_status
    sql: ${TABLE}.PatientStatus
  - dimension: pharmacy_provider_code
    sql: ${TABLE}.PharmacyProviderCode
  - dimension: pharmacy_provider_name
    sql: ${TABLE}.PharmacyProviderName
  - dimension: pharmacy_type
    sql: ${TABLE}.PharmacyType
  - dimension: place_of_service
    sql: ${TABLE}.PlaceOfService
  - dimension: plan_code
    sql: ${TABLE}.PlanCode
  - dimension: plan_year
    type: int
    sql: ${TABLE}.PlanYear
  - dimension: prescribing_provider_code
    sql: ${TABLE}.PrescribingProviderCode
  - dimension: prescribing_provider_name
    sql: ${TABLE}.PrescribingProviderName
  - dimension: procedure_code
    sql: ${TABLE}.ProcedureCode
  - dimension: procedure_modifier
    sql: ${TABLE}.ProcedureModifier
  - dimension: provider_code
    sql: ${TABLE}.ProviderCode
  - dimension: provider_name
    sql: ${TABLE}.ProviderName
  - dimension: provider_tax_id
    sql: ${TABLE}.ProviderTaxId
  - dimension: provider_type
    sql: ${TABLE}.ProviderType
  - dimension: quantity_of_services
    type: int
    sql: ${TABLE}.QuantityOfServices
  - dimension: revcode
    sql: ${TABLE}.REVCode
  - dimension: sales_tax
    sql: ${TABLE}.SalesTax
  - dimension: specialty_code
    sql: ${TABLE}.SpecialtyCode
  - dimension: underwriter
    sql: ${TABLE}.Underwriter
  - dimension: zipcode
    sql: ${TABLE}.ZIPCode
  - dimension_group: admission
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.AdmissionDate
  - dimension_group: date_filled
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DateFilled
  - dimension_group: date_incurred_from
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DateIncurredFrom
  - dimension_group: date_incurred_to
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DateIncurredTo
  - dimension_group: date_paid
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    - dow
    sql: ${TABLE}.DatePaid
  - dimension_group: date_tag
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DateTag
  - dimension_group: date_written
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DateWritten
  - dimension_group: discharge
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.DischargeDate
  - measure: count
    type: count
  - measure: number_of_claims
    type: count_distinct
    sql: ${claim_no}
  - measure: plan_paid
    type: sum
    sql: ${company_paid}
  - measure: unique_claimants
    type: count_distinct
    sql: ${member_id}
  - measure: claims_per_claimants
    type: number
    sql: ${number_of_claims} / ${unique_claimants}
  - measure: paid_per_claimant
    type: number
    sql: ${plan_paid} / ${unique_claimants}

Contents

  • claim_line.view.lkml
  • claim_line.view.lookml