Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

dmelillo
Contributor II

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!

Tags (1)
1 Solution

Accepted Solutions

Re: Restrict LOAD to IDs in Resident Table

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

23 Replies

Re: Restrict LOAD to IDs in Resident Table

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

dmelillo
Contributor II

Re: Restrict LOAD to IDs in Resident Table

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?

Re: Restrict LOAD to IDs in Resident Table

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

dmelillo
Contributor II

Re: Restrict LOAD to IDs in Resident Table

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

Re: Restrict LOAD to IDs in Resident Table

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

dmelillo
Contributor II

Re: Restrict LOAD to IDs in Resident Table

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

Re: Restrict LOAD to IDs in Resident Table

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

dmelillo
Contributor II

Re: Restrict LOAD to IDs in Resident Table

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

thank you sir

dmelillo
Contributor II

Re: Restrict LOAD to IDs in Resident Table

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

Community Browser