Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Qlik Mail Chimp Connector

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?

7 Replies
Highlighted
Specialist
Specialist

Re: Qlik Mail Chimp Connector

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.

Highlighted
Contributor II
Contributor II

Re: Qlik Mail Chimp Connector

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

Highlighted
Specialist
Specialist

Re: Qlik Mail Chimp Connector

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?

Highlighted
Contributor II
Contributor II

Re: Qlik Mail Chimp Connector

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

Highlighted
Specialist
Specialist

Re: Qlik Mail Chimp Connector

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:

datamodel.png

Highlighted
Specialist
Specialist

Re: Qlik Mail Chimp Connector

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
Highlighted
Specialist
Specialist

Re: Qlik Mail Chimp Connector

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.datamodel.png