Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Help in Concatenate , Join statements

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 :

Tags (2)
2 Replies
manojkvrajan
Not applicable

Re: Help in Concatenate , Join statements

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
Not applicable

Re: Help in Concatenate , Join statements

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