Discussion board where members can learn more about Qlik Sense App Development and Usage.
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:
SELECT * FROM Lead
WHERE Id IN (SELECT DISTINCT Id FROM ResidentTable)
Any help is appreciated!
Go to Solution.
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
WHERE Id IN
Remove the single quotes in Red above
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
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?
Something like this
LOAD Concat(DISTINCT Chr(39) & ID & Chr(39), ',') as List;
FROM campaign in salesforce;
LET vList = Peek('List');
WHERE Id IN ('$(vList)')
I have tried below but it is not working. The Below is only creating one row so maybe I am misunderstanding something ...
LOAD Concat(DISTINCT Chr(39) & IF(IsNull(ContactId), LeadId, ContactId) & Chr(39), ',') as List;
WHERE CampaignId = '7011O0000021IGvQAM';
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
Then something is going wrong in the second part, I will have to trouble shoot
Can you add TRACE to make sure the variable looks good
Yes it looks like the variables and not being created ... I am going to use trace and revert the findings
thank you sir
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
LOAD Concat(DISTINCT Chr(39) & LeadId & Chr(39), ',') as ListLead,
LET vListLead = Peek('ListLead');
LOAD Concat(DISTINCT Chr(39) & ContactId & Chr(39), ',') as ListContact,
LET vListContact = Peek('ListContact');