0 Replies Latest reply: Jun 11, 2018 5:29 AM by Sarah Hawkins RSS

    Join Table on 1 key OR another Key

    Sarah Hawkins

      Hi All,

       

      I have a table that can have multiple different Scenerios to join onto the fact table.

       

      For example I have 4 columns in Table A to link to my fact table;

      Office & "Product target" & Insurer &"Business Event"

       

      OfficeProduct TargetInsurerBusiness EventOther FieldOther fieldValue
      All OfficesAll ProductsAll InsurersNBAA10
      All OfficesAll ProductsInsurer ANBBB15
      All OfficesAll ProductsInsurer BNBCC13
      All OfficesProduct AInsurer ARENDD12
      Office AProduct BInsurer BNBEE11

       

      In my fact Table I can force the values to be the below

      'All Offices' & 'All Products' & 'All Insurer' & 'Business Event' AS Key to be able to link everything to the top line, using a where clause on Table A.  ('All Offices', 'All Products', 'All Insurers' values does not exist in my fact table)

       

      However, how do I then change my key to look at the Insurer Column and not manually set the value to 'All Insurer' (In the table above the second and third line of data). Where Insurer A exists in my fact table the value should be 15 and not the 10 it is currently linked to.

      'All Offices' & 'All Products' & Insurer & 'Business Event'

      Without the 'Other Fields' being synthetically joined. 'Other Fields' are being used in data model.

       

       

      Thanks in Advance,

       

      Sarah