Successive Joins Not Working

[#1]

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]);

[swuehl]

Are you sure you want leads_created to be a key field for the JOIN?

Maybe explain your setting with a few lines of sample records for your input tables and also post your expected result.

[#3]

Yes, I want the cost metric to be applied to all new leads, so that later when I aggregate the data I can get a simple sum() of the cost, rather than needing to multiply the cost_pl by the sum of the leads_created.

[swuehl]

Ah, you are renaming your costs to the same field name? That will result in a fourth key field...

[swuehl · Accepted solution]

Maybe first concatenate your two cost estimate tables, then JOIN the concatenated table to the metrics table.

[swuehl]

Also avoiding a JOIN completely should be possible:

Don't join - use Applymap instead

[#7]

That did it. Still don't understand why only one of the two joins work when they're structured identically, but at least I got this report working. Thanks!