Restrict LOAD to IDs in Resident Table

[Anonymous]

In my Qlik Sense app, I am FIRST loading a list campaign members from one campaign in salesforce.

I want to load ONLY lead/contact information from people who are a part of that campaign.

I would imagine the syntax to be something like below, but I know this is not correct:

LOAD

@1,

@2,

@3;

SELECT * FROM Lead

WHERE Id IN (SELECT DISTINCT Id FROM ResidentTable)

Any help is appreciated!

[sunny_talwar]

So you looking to restrict them directly in your SQL? may be save the list of IDs in a variable separated by comma and then use that in your where clause the way you just used above

[Anonymous]

I am looking to restrict them any way I can. I do not know how to save the IDs in a variable separated by commas. Is there an article you can link me to?

[sunny_talwar]

Something like this

LOAD Concat(DISTINCT Chr(39) & ID & Chr(39), ',') as List;

SELECT ID

FROM campaign in salesforce;

LET vList = Peek('List');

LOAD

@1,

@2,

@3;

SELECT * FROM Lead

WHERE Id IN ('$(vList)')

[Anonymous]

I have tried below but it is not working. The Below is only creating one row so maybe I am misunderstanding something ...

List:

LOAD Concat(DISTINCT Chr(39) & IF(IsNull(ContactId), LeadId, ContactId) & Chr(39), ',') as List;

SELECT *

FROM CampaignMember

WHERE CampaignId = '7011O0000021IGvQAM';

LET vList = Peek('List');

[sunny_talwar]

It will contain only one row, but will concat all your Ids to check into a comma separate list which can then be supplied into your SQL

[Anonymous]

Then something is going wrong in the second part, I will have to trouble shoot

[sunny_talwar]

Can you add TRACE to make sure the variable looks good

List:

LOAD Concat(DISTINCT Chr(39) & IF(IsNull(ContactId), LeadId, ContactId) & Chr(39), ',') as List;

SELECT *

FROM CampaignMember

WHERE CampaignId = '7011O0000021IGvQAM';

LET vList = Peek('List');

TRACE $(vList);

[Anonymous]

Yes it looks like the variables and not being created ... I am going to use trace and revert the findings

thank you sir

[Anonymous]

I am not sure what TRACE is meant to do but the script did not work. I can tell the values are being created correctly by the load statement because I can see the List field in a table box after load.

I dont think the variables are being created. Below is the total script

ListLead:

LOAD Concat(DISTINCT Chr(39) & LeadId & Chr(39), ',') as ListLead,

;

SELECT *

FROM CampaignMember

WHERE CampaignId = '7011O0000021IGvQAM';

LET vListLead = Peek('ListLead');

TRACE $(vListLead);

ListContact:

LOAD Concat(DISTINCT Chr(39) & ContactId & Chr(39), ',') as ListContact,

;

SELECT *

FROM CampaignMember

WHERE CampaignId = '7011O0000021IGvQAM';

LET vListContact = Peek('ListContact');

TRACE $(vListContact);