topic Re: Restrict LOAD to IDs in Resident Table in App Development

Restrict LOAD to IDs in Resident Table

Anonymous — Tue, 10 Oct 2017 18:39:53 GMT

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!

Re: Restrict LOAD to IDs in Resident Table

sunny_talwar — Tue, 10 Oct 2017 18:41:25 GMT

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

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Tue, 10 Oct 2017 18:42:58 GMT

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

sunny_talwar — Tue, 10 Oct 2017 18:45:54 GMT

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

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Tue, 10 Oct 2017 19:32:25 GMT

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

sunny_talwar — Tue, 10 Oct 2017 19:39:44 GMT

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

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Tue, 10 Oct 2017 19:52:27 GMT

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

Re: Restrict LOAD to IDs in Resident Table

sunny_talwar — Tue, 10 Oct 2017 19:53:55 GMT

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

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Tue, 10 Oct 2017 20:04:10 GMT

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

thank you sir

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Tue, 10 Oct 2017 20:06:35 GMT

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

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Tue, 10 Oct 2017 20:14:08 GMT

Figured it out. I had to qualify the field in the PEEK

LET vListContact = Peek('ListContact.ListContact');

Re: Restrict LOAD to IDs in Resident Table

sunny_talwar — Tue, 10 Oct 2017 20:56:17 GMT

So, is it working now?

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Tue, 10 Oct 2017 21:00:35 GMT

Not yet. Some the values stored in the variable is throwing an error.

Re: Restrict LOAD to IDs in Resident Table

sunny_talwar — Tue, 10 Oct 2017 21:01:57 GMT

Oh really? What error message do you get?

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Thu, 12 Oct 2017 12:29:21 GMT

INVALID_QUERY_FILTER_OPERATOR: LID__LinkedIn_Member_Token__c FROM Lead WHERE Id IN ('\'\',\'00Q1400001QVqbXEAT\' ^ ERROR at Row:1:Column:7304 invalid ID field:

Re: Restrict LOAD to IDs in Resident Table

sunny_talwar — Thu, 12 Oct 2017 12:34:25 GMT

What is ID field here? Why is that field invalid?

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Thu, 12 Oct 2017 12:34:35 GMT

Just understanding simple SQL ... it might be an issue where the last ',' in the IN statement is possibly causing and issue, but I am not sure. If there are suggestions in how I can zoom in to the exact place of error that would be great. I might be able to figure it out myself if I can drill deeper ...

Re: Restrict LOAD to IDs in Resident Table

sunny_talwar — Thu, 12 Oct 2017 12:37:23 GMT

Where are you seeing the last ','? Just look at the error message doesn't really show me the complete picture... may be share your script up until where you run this SQL

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Thu, 12 Oct 2017 12:47:00 GMT

When we create the variables below we CONCAT ',' to every Id ... But for the IN statement to work the last ID must NOT have a ','

i.e WHERE Id IN ('1','2','3')

ListLead:

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

;

SELECT *

FROM CampaignMember

WHERE CampaignId = '7011O0000021IGvQAM';

LET vListLead = Peek('ListLead.ListLead');

TRACE $(vListLead);

ListContact:

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

;

SELECT *

FROM CampaignMember

WHERE CampaignId = '7011O0000021IGvQAM';

LET vListContact = Peek('ListContact.ListContact');

TRACE $(vListContact);

Below is where I want to use the variable to only select IDs identified in a specific campaign:

Who:

UNQUALIFY *;

LOAD Id as WhoId,

Id as WhoId2,

Title as WhoTitle,

Email as WhoEmail,

LeanData__Reporting_Matched_Account__c as AccountId,

Company;

SELECT *

FROM Lead

WHERE Id IN

('$(vListLead)');

Concatenate(Who)Add

LOAD Id as WhoId,

Id as WhoId2,

AccountId,

Title as WhoTitle,

Email as WhoEmail;

SELECT *

FROM Contact

WHERE Id IN

('$(vListContact)');

EXIT SCRIPT;

Re: Restrict LOAD to IDs in Resident Table

Anonymous — Thu, 12 Oct 2017 13:06:39 GMT

Something is wrong with the way ID is being concatenated .. Below is an excerpt of how the IDs look in the statement

SELECT *

FROM Lead

WHERE Id IN

(''''',''00Q1400001QVqbXEAT'',''00Q1400001SItOSEA1'',''00Q1400001SJYKkEAP'',''00Q1400001XQSBZEA5'',''00Q1400001XQSBbEAP'',''00Q1400001XQSBeEAP