Help in Concatenate , Join statements

[#1]

I have 3 files

File 1 : List of 100 campaigns

File 2 : List of Leads for some of the campaigns

File 3 : List of opportunities for some of the campaigns

(common field between all 3 is Campaign ID)

My output :

List of all campaigns and their corresponding Lead count and opportunity count

Some cases there will be only leads for the campaigns , or only opportunities for the campaigns

or neither

Yet  i want to see all campaigns irrespective of whether i have leads or opportunities

Even full join doesnt give me the full list .( Unfortunately i dont get campaigns which doesnt have leads or opportunities)

Help...

I tried the foll code, but not working

Lead :

Load *

Concatenate :

Load *

Join

Campaign :

[manojkvrajan]

A simple join should work though it will fill null values in the Campaign table in unavailable values in Lead and Opportunities column. Please send me a sample dataset and qvw and let me see how can I help you with.

Campaign:

Load CampaignID, Desc

FROM Campaign

join

LOAD CampaignID,

Lead

FROM Lead

WHERE EXISTS (CampaignID, CampaignID)

join

LOAD CampaignID,

Opportunities

FROM Opportunities

WHERE EXISTS (CampaignID, CampaignID)

[rwunderlich]

In your chart(s), make sure you uncheck "suppress zero values" on the presentation tab so Campaigns with zero leads and opportunities will show.

-Rob