Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Successive Joins Not Working

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Successive Joins Not Working

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

6 Replies
MVP
MVP

Re: Successive Joins Not Working

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.

Not applicable

Re: Successive Joins Not Working

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.

MVP
MVP

Re: Successive Joins Not Working

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

MVP
MVP

Re: Successive Joins Not Working

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

MVP
MVP

Re: Successive Joins Not Working

Also avoiding a JOIN completely should be possible:

Don't join - use Applymap instead

Not applicable

Re: Successive Joins Not Working

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!

Community Browser