Qlik Mail Chimp Connector

[EloyVU]

Saben como puedo cargar todas las tablas del conector, sin tener que cargar manualmente cada que haya un objeto nuevo?

Do you know how I can load all the connector tables, without having to manually load each new object?

[chrisbrain]

Hi - I am not sure I completely understand your question. But perhaps you are asking if there is a way to get all of the data the connector could provide without having to manually run each table and insert the script into your app?

Unfortunately this is not possible - some of the tables have different input parameters - for example list ID or campaign ID. So you would first need to for example, load the Campaigns table and then construct a for/next loop to iterate through each campaign ID and then run each one through another table such as CampaignReport.

[EloyVU]

Thank you very much, I have tried to do this load but I cannot load them, an example would help me a lot because I know it works but I cannot find the way
to apply it correctly

[chrisbrain]

I can try and put together a quick sample which might give you the idea - can you just confirm are you referring to the Qlik Web Connectors standalone version or the version integrated into Qlik Sense?

[EloyVU]

I am working with MailchimpV2 connector.
But when I try to get the information from the MemberInfo table the method that
offers the connector only returns one account at a time ...

In the query that generates the connector .. at the end bring this From ... & listId = 3db5105fa9 & email = mail% domain.com & appID =]
How can I pass the value of the mail by means of a variable ... to use a for i ... and sweep mail by mail ...
What would be the method to extract the complete information from the MemberInfo table?
I am connecting to Qlik View

[chrisbrain]

Hi,

The following script should help you - please note the comments in it regarding the fact that there are many more columns available in these tables.

Lists:
LOAD
    id as Lists_id,
    name as Lists_name,
    contact_company as Lists_contact_company
	// NOTE: Most available columns have been removed from this sample script for clarity.
	// Regenerate load script in connector to get full column list.
FROM
[http://localhost:5555/data?connectorID=MailChimpConnectorV2&table=Lists&appID=]
(qvx);


LET noLists = NoOfRows('Lists');
	
for i=0 to $(noLists)-1
		
	let listId = peek('Lists_id', $(i), 'Lists');
	
	trace 'Getting List $(listId)';
	
	List:
	LOAD
		'$(listId)' as Lists_id,
	    [Email Address] as [List_Email Address],
	    // NOTE: Most available columns have been removed from this sample script for clarity.
	    // Regenerate load script in connector to get full column list.
	    [First Name] as [List_First Name],
	    [Last Name] as [List_Last Name]
	FROM
	[http://localhost:5555/data?connectorID=MailChimpConnectorV2&table=List&listId=$(listId)&appID=]
	(qvx);
	
	let noEmails = NoOfRows('List');
	
	// Useful when testing script to not have to wait to process all emails.
	let maxEmailsToProcess = RangeMin(noEmails, 100);
	
	for j=0 to $(maxEmailsToProcess)-1
			
		let emailAddress = peek('List_Email Address', $(j), 'List');
		
		// + in email address breaks next request so URL encode it.
		let emailAddressEncoded = replace(emailAddress, '+', '%2B');
		
		trace 'Getting details for $(emailAddress)';
		
		MemberInfo:
		LOAD
		    id as MemberInfo_id,
			// email_address as MemberInfo_email_address,
		    email_address as [List_Email Address],
		    // NOTE: Most available columns have been removed from this sample script for clarity.
		    // Regenerate load script in connector to get full column list.
		    unique_email_id as MemberInfo_unique_email_id,
		    email_type as MemberInfo_email_type,
		    status as MemberInfo_status,
		    interests as MemberInfo_interests,
		    stats_avg_open_rate as MemberInfo_stats_avg_open_rate,
		    stats_avg_click_rate as MemberInfo_stats_avg_click_rate,
		    ip_signup as MemberInfo_ip_signup,
		    timestamp_signup as MemberInfo_timestamp_signup,
		    ip_opt as MemberInfo_ip_opt,
		    timestamp_opt as MemberInfo_timestamp_opt,
		    member_rating as MemberInfo_member_rating,
		    last_changed as MemberInfo_last_changed,
		    language as MemberInfo_language,
		    location_country_code as MemberInfo_location_country_code,
		    location_timezone as MemberInfo_location_timezone
		FROM
		[http://localhost:5555/data?connectorID=MailChimpConnectorV2&table=MemberInfo&listId=$(listId)&email=$(emailAddressEncoded)&appID=]
		(qvx);
		
	next
	
next

This should create the following data model:

[chrisbrain]

Hi - The first time I tried to post this it got marked as spam! So I'll try again breaking it down into 2 posts.

So this script might give you an idea of how to get the data you need:

Lists:
LOAD
    id as Lists_id,
    name as Lists_name,
    contact_company as Lists_contact_company
	// NOTE: Most available columns have been removed from this sample script for clarity.
	// Regenerate load script in connector to get full column list.
FROM
[http://localhost:5555/data?connectorID=MailChimpConnectorV2&table=Lists&appID=]
(qvx);


LET noLists = NoOfRows('Lists');
	
for i=0 to $(noLists)-1
		
	let listId = peek('Lists_id', $(i), 'Lists');
	
	trace 'Getting List $(listId)';
	
	List:
	LOAD
		'$(listId)' as Lists_id,
	    [Email Address] as [List_Email Address],
	    // NOTE: Most available columns have been removed from this sample script for clarity.
	    // Regenerate load script in connector to get full column list.
	    [First Name] as [List_First Name],
	    [Last Name] as [List_Last Name]
	FROM
	[http://localhost:5555/data?connectorID=MailChimpConnectorV2&table=List&listId=$(listId)&appID=]
	(qvx);
	
	let noEmails = NoOfRows('List');
	
	// Useful when testing script to not have to wait to process all emails.
	let maxEmailsToProcess = RangeMin(noEmails, 100);
	
	for j=0 to $(maxEmailsToProcess)-1
			
		let emailAddress = peek('List_Email Address', $(j), 'List');
		
		// + in email address breaks next request so URL encode it.
		let emailAddressEncoded = replace(emailAddress, '+', '%2B');
		
		trace 'Getting details for $(emailAddress)';
		
		MemberInfo:
		LOAD
		    id as MemberInfo_id,
			// email_address as MemberInfo_email_address,
		    email_address as [List_Email Address],
		    // NOTE: Most available columns have been removed from this sample script for clarity.
		    // Regenerate load script in connector to get full column list.
		    unique_email_id as MemberInfo_unique_email_id,
		    email_type as MemberInfo_email_type,
		    status as MemberInfo_status,
		    interests as MemberInfo_interests,
		    stats_avg_open_rate as MemberInfo_stats_avg_open_rate,
		    stats_avg_click_rate as MemberInfo_stats_avg_click_rate,
		    ip_signup as MemberInfo_ip_signup,
		    timestamp_signup as MemberInfo_timestamp_signup,
		    ip_opt as MemberInfo_ip_opt,
		    timestamp_opt as MemberInfo_timestamp_opt,
		    member_rating as MemberInfo_member_rating,
		    last_changed as MemberInfo_last_changed,
		    language as MemberInfo_language,
		    location_country_code as MemberInfo_location_country_code,
		    location_timezone as MemberInfo_location_timezone
		FROM
		[http://localhost:5555/data?connectorID=MailChimpConnectorV2&table=MemberInfo&listId=$(listId)&email=$(emailAddressEncoded)&appID=]
		(qvx);
		
	next
	
next

[chrisbrain]

And the above script should create the following data model. As noted in script above, I have removed a lot of the fields for clarity.