Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Restrict LOAD to IDs in Resident Table

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!

1 Solution

Accepted Solutions
sunny_talwar

Concat function doesn't add a comma after the last value... so you don't need to worry about that....

Can you try without single quotes here

SELECT *

FROM Lead

WHERE Id IN

('$(vListLead)');

Remove the single quotes in Red above

View solution in original post

23 Replies
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
Not applicable
Author

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

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

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

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

thank you sir

Anonymous
Not applicable
Author

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