topic Re: Successive Joins Not Working in QlikView

Successive Joins Not Working

Tue, 22 Aug 2017 17:38:36 GMT

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

Re: Successive Joins Not Working

swuehl — Tue, 22 Aug 2017 19:22:18 GMT

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.

Re: Successive Joins Not Working

Tue, 22 Aug 2017 19:25:21 GMT

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.

Re: Successive Joins Not Working

swuehl — Tue, 22 Aug 2017 19:30:38 GMT

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

Re: Successive Joins Not Working

swuehl — Tue, 22 Aug 2017 19:32:37 GMT

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

Re: Successive Joins Not Working

swuehl — Tue, 22 Aug 2017 19:34:39 GMT

Also avoiding a JOIN completely should be possible:

Don't join - use Applymap instead

Re: Successive Joins Not Working

Tue, 22 Aug 2017 19:53:06 GMT

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!