6 Replies Latest reply: Aug 22, 2017 3:53 PM by Mike Hernandez RSS

    Successive Joins Not Working

    Mike Hernandez

      I have a reference table (maintained by another user so I can't alter it) that I need to join to my fact table. There are three fields that need to match for the join, and the first join works as expected, however the third load statement is not joining on all three columns. If the Join is Outer it gives leads_created as NULL, and if a Left Join is used that leads_created is NULL means none of the tv_cash_cost_pl data is loaded.

       

      qv_metrics:

      LOAD lead_id

      , dim_date_key_activity

      , initial_channel

      , leads_created

      , leads_sent_to_field

      , referred_lead

      FROM $(QVDPath)\qv_metrics.qvd(qvd);

       

      JOIN (qv_metrics)

      LOAD dim_date_key_activity

      , 'TV-PI' AS initial_channel

      , 1 AS leads_created

      , tv_pi_cost_pl AS marketing_tv_pl

      FROM [$(MetricDefinitionPath)\TVCostEstimates.xlsx]

      (ooxml, embedded labels, table is [TVEstimates]);

       

      JOIN (qv_metrics)

      LOAD dim_date_key_activity

      , 'TV-Cash' AS initial_channel

      , 1 AS leads_created

      , tv_cash_cost_pl AS marketing_tv_pl

      FROM [$(MetricDefinitionPath)\TVCostEstimates.xlsx]

      (ooxml, embedded labels, table is [TVEstimates]);