Looping through table using REST API

tmumaw — Tue, 11 Sep 2018 13:58:16 GMT

Hi all,

Need a little help. I need to loop through a table in Microsoft CRM365. Does anyone have an example of how to do this? The loop, I think I can figure out the rest.

Thanks....

Re: Looping through table using REST API

petter — Tue, 11 Sep 2018 14:43:19 GMT

Pagination is probably what you are looking for:

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Create-REST-connection/Pag…

With "auto-pagination" the REST connector will "loop" repeatedly to send multiple requests to get the results.

With manual pagination you can do the looping yourself and modify the URL and or the POST content of the REST SQL statement by using the WITH CONNECTION clause.

Re: Looping through table using REST API

tmumaw — Wed, 12 Sep 2018 11:11:43 GMT

I have never used the REST connector before. So this is a first time for me. Here is what I have so far. Any help would be appreciated.

Connect String

RestConnectorMasterTable:
SQL SELECT
"token_type",
"expires_in",
"ext_expires_in",
"expires_on",
"not_before",
"resource",
"access_token"
FROM JSON (wrap on) "root";

[root]:
LOAD [token_type],
[expires_in],
[ext_expires_in],
[expires_on],
[not_before],
[resource],
[access_token]
RESIDENT RestConnectorMasterTable;

LET vToken = peek('access_token');

DROP TABLE RestConnectorMasterTable;

LET accountsURL = 'https://daveyqas.api.crm.dynamics.com/api/data/v9.0/accounts';

CUSTOM CONNECT TO

RestConnectorMasterTable:
SQL SELECT
"@odata.context",
"__KEY_root",
(SELECT
"@odata.etag",
"paymenttermscode",
"davey_saptext",
"mcala_lastemaildate",
"address1_line1",
"mcala_lastactivitytype",
"merged",
"davey_accountgroup",
"_modifiedby_value",
"territorycode",
"emailaddress1",
"davey_donotvalidateaddress",
"exchangerate",
"davey_createnewprimarycontact",
"davey_cpbusinesspartner",
"mcala_lastactivitydate",
"name",
"accountcategorycode",
"_owningbusinessunit_value",
"_primarycontactid_value",
"davey_source",
"davey_stateprovince",
"address1_composite",
"davey_accountbalance_base",
"donotpostalmail",
"accountratingcode",
"marketingonly",
"davey_noupdatetobillingscheduleflag",
"donotphone",
"preferredcontactmethodcode",
"_ownerid_value",
"address1_telephone1",
"description",
"davey_clientrelationshipfolder",
"customersizecode",
"davey_lastpaymentamount",
"openrevenue_date",
"address2_addresstypecode",
"businesstypecode",
"donotemail",
"address2_shippingmethodcode",
"timezoneruleversionnumber",
"address1_addressid",
"davey_renewalallowed",
"address2_freighttermscode",
"statuscode",
"createdon",
"versionnumber",
"address1_stateorprovince",
"davey_renewalmonth",
"davey_baddebt",
"davey_country",
"donotsendmm",
"donotfax",
"davey_clientsince",
"donotbulkpostalmail",
"davey_addressaccepted",
"address1_country",
"davey_taxexempted",
"followemail",
"davey_evergreenstatus",
"address1_line2",
"modifiedon",
"creditonhold",
"_davey_salesarborist_value",
"davey_bill_sched_block",
"_transactioncurrencyid_value",
"accountid",
"davey_addressunvalidated",
"donotbulkemail",
"accountclassificationcode",
"davey_accountname2",
"davey_daveyemployeeflag",
"davey_clientclass",
"mca_sapaccountid",
"shippingmethodcode",
"mcala_nextapptdate",
"mcala_nextactivitytype",
"_createdby_value",
"address1_city",
"davey_sortzip",
"mcala_nextactivitydate",
"davey_industrycode",
"davey_renewaldeliverypreference",
"davey_blockedforsales",
"telephone1",
"davey_ranrenewalyear",
"_owningteam_value",
"participatesinworkflow",
"davey_lastpaymentamount_base",
"statecode",
"overriddencreatedon",
"address2_addressid",
"address1_postalcode",
"davey_accountbalance",
"davey_lastpaymentdate",
"opendeals_date",
"davey_sortterm",
"aging60",
"mca_datesenttosap",
"address2_upszone",
"sharesoutstanding",
"address2_stateorprovince",
"address1_longitude",
"importsequencenumber",
"address2_longitude",
"sic",
"address2_telephone2",
"entityimage",
"address2_composite",
"entityimage_timestamp",
"traversedpath",
"primarytwitterid",
"marketcap_base",
"davey_servicecontactpreference",
"davey_propertycreatememo",
"openrevenue",
"address1_line3",
"fax",
"stageid",
"_preferredsystemuserid_value",
"customertypecode",
"numberofemployees",
"emailaddress2",
"processid",
"address2_primarycontactname",
"address2_utcoffset",
"davey_callfirst",
"address2_city",
"stockexchange",
"creditlimit_base",
"address1_latitude",
"timespentbymeonemailandmeetings",
"address2_name",
"utcconversiontimezonecode",
"davey_marketingchannel",
"_originatingleadid_value",
"address2_postofficebox",
"telephone2",
"address2_latitude",
"revenue",
"entityimageid",
"address2_fax",
"address2_telephone1",
"address1_shippingmethodcode",
"lastusedincampaign",
"address1_telephone3",
"entityimage_url",
"marketcap",
"address2_county",
"address1_freighttermscode",
"openrevenue_base",
"address1_primarycontactname",
"davey_arboristsrenewalnotes",
"mcala_lasttaskdate",
"tickersymbol",
"_davey_propertyid_value",
"_parentaccountid_value",
"creditlimit",
"industrycode",
"accountnumber",
"davey_crmaudit",
"address2_telephone3",
"aging90",
"aging90_base",
"_preferredequipmentid_value",
"_defaultpricelevelid_value",
"_slainvokedid_value",
"address1_telephone2",
"address2_line1",
"address1_postofficebox",
"openrevenue_state",
"address1_utcoffset",
"ownershipcode",
"davey_primarycontactfullname",
"_territoryid_value",
"davey_specialcustomer",
"_slaid_value",
"revenue_base",
"_owninguser_value",
"davey_acquisitioncode",
"_modifiedbyexternalparty_value",
"opendeals_state",
"davey_jobsitetext",
"onholdtime",
"aging30_base",
"mcala_nextphonecalldate",
"_preferredserviceid_value",
"aging60_base",
"davey_referredbynote",
"davey_referredon",
"address1_name",
"_modifiedonbehalfby_value",
"yominame",
"address1_addresstypecode",
"address1_county",
"_createdonbehalfby_value",
"address2_line2",
"opendeals",
"ftpsiteurl",
"address2_country",
"aging30",
"_davey_previousaccount_value",
"davey_laststatusupdatedate",
"address1_fax",
"davey_marketingsubchannel",
"davey_accounttype",
"primarysatoriid",
"mcala_nexttaskdate",
"preferredappointmenttimecode",
"preferredappointmentdaycode",
"_createdbyexternalparty_value",
"_masterid_value",
"emailaddress3",
"address1_upszone",
"_davey_referredbyid_value",
"mcala_lastphonecalldate",
"davey_acquiredfrom",
"mcala_lastapptdate",
"address2_postalcode",
"websiteurl",
"lastonholdtime",
"telephone3",
"address2_line3",
"__FK_value"
FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root";

[Account]:
LOAD [@odata.etag],
[paymenttermscode],
[davey_saptext],
[mcala_lastemaildate],
[address1_line1],
[mcala_lastactivitytype],
[merged],
[davey_accountgroup],
[_modifiedby_value],
[territorycode],
[emailaddress1],
[davey_donotvalidateaddress],
[exchangerate],
[davey_createnewprimarycontact],
[davey_cpbusinesspartner],
[mcala_lastactivitydate],
[name],
[accountcategorycode],
[_owningbusinessunit_value],
[_primarycontactid_value],
[davey_source],
[davey_stateprovince],
[address1_composite],
[davey_accountbalance_base],
[donotpostalmail],
[accountratingcode],
[marketingonly],
[davey_noupdatetobillingscheduleflag],
[donotphone],
[preferredcontactmethodcode],
[_ownerid_value],
[address1_telephone1],
[description],
[davey_clientrelationshipfolder],
[customersizecode],
[davey_lastpaymentamount],
[openrevenue_date],
[address2_addresstypecode],
[businesstypecode],
[donotemail],
[address2_shippingmethodcode],
[timezoneruleversionnumber],
[address1_addressid],
[davey_renewalallowed],
[address2_freighttermscode],
[statuscode],
[createdon],
[versionnumber],
[address1_stateorprovince],
[davey_renewalmonth],
[davey_baddebt],
[davey_country],
[donotsendmm],
[donotfax],
[davey_clientsince],
[donotbulkpostalmail],
[davey_addressaccepted],
[address1_country],
[davey_taxexempted],
[followemail],
[davey_evergreenstatus],
[address1_line2],
[modifiedon],
[creditonhold],
[_davey_salesarborist_value],
[davey_bill_sched_block],
[_transactioncurrencyid_value],
[accountid],
[davey_addressunvalidated],
[donotbulkemail],
[accountclassificationcode],
[davey_accountname2],
[davey_daveyemployeeflag],
[davey_clientclass],
[mca_sapaccountid],
[shippingmethodcode],
[mcala_nextapptdate],
[mcala_nextactivitytype],
[_createdby_value],
[address1_city],
[davey_sortzip],
[mcala_nextactivitydate],
[davey_industrycode],
[davey_renewaldeliverypreference],
[davey_blockedforsales],
[telephone1],
[davey_ranrenewalyear],
[_owningteam_value],
[participatesinworkflow],
[davey_lastpaymentamount_base],
[statecode],
[overriddencreatedon],
[address2_addressid],
[address1_postalcode],
[davey_accountbalance],
[davey_lastpaymentdate],
[opendeals_date],
[davey_sortterm],
[aging60],
[mca_datesenttosap],
[address2_upszone],
[sharesoutstanding],
[address2_stateorprovince],
[address1_longitude],
[importsequencenumber],
[address2_longitude],
[sic],
[address2_telephone2],
[entityimage],
[address2_composite],
[entityimage_timestamp],
[traversedpath],
[primarytwitterid],
[marketcap_base],
[davey_servicecontactpreference],
[davey_propertycreatememo],
[openrevenue],
[address1_line3],
[fax],
[stageid],
[_preferredsystemuserid_value],
[customertypecode],
[numberofemployees],
[emailaddress2],
[processid],
[address2_primarycontactname],
[address2_utcoffset],
[davey_callfirst],
[address2_city],
[stockexchange],
[creditlimit_base],
[address1_latitude],
[timespentbymeonemailandmeetings],
[address2_name],
[utcconversiontimezonecode],
[davey_marketingchannel],
[_originatingleadid_value],
[address2_postofficebox],
[telephone2],
[address2_latitude],
[revenue],
[entityimageid],
[address2_fax],
[address2_telephone1],
[address1_shippingmethodcode],
[lastusedincampaign],
[address1_telephone3],
[entityimage_url],
[marketcap],
[address2_county],
[address1_freighttermscode],
[openrevenue_base],
[address1_primarycontactname],
[davey_arboristsrenewalnotes],
[mcala_lasttaskdate],
[tickersymbol],
[_davey_propertyid_value],
[_parentaccountid_value],
[creditlimit],
[industrycode],
[accountnumber],
[davey_crmaudit],
[address2_telephone3],
[aging90],
[aging90_base],
[_preferredequipmentid_value],
[_defaultpricelevelid_value],
[_slainvokedid_value],
[address1_telephone2],
[address2_line1],
[address1_postofficebox],
[openrevenue_state],
[address1_utcoffset],
[ownershipcode],
[davey_primarycontactfullname],
[_territoryid_value],
[davey_specialcustomer],
[_slaid_value],
[revenue_base],
[_owninguser_value],
[davey_acquisitioncode],
[_modifiedbyexternalparty_value],
[opendeals_state],
[davey_jobsitetext],
[onholdtime],
[aging30_base],
[mcala_nextphonecalldate],
[_preferredserviceid_value],
[aging60_base],
[davey_referredbynote],
[davey_referredon],
[address1_name],
[_modifiedonbehalfby_value],
[yominame],
[address1_addresstypecode],
[address1_county],
[_createdonbehalfby_value],
[address2_line2],
[opendeals],
[ftpsiteurl],
[address2_country],
[aging30],
[_davey_previousaccount_value],
[davey_laststatusupdatedate],
[address1_fax],
[davey_marketingsubchannel],
[davey_accounttype],
[primarysatoriid],
[mcala_nexttaskdate],
[preferredappointmenttimecode],
[preferredappointmentdaycode],
[_createdbyexternalparty_value],
[_masterid_value],
[emailaddress3],
[address1_upszone],
[_davey_referredbyid_value],
[mcala_lastphonecalldate],
[davey_acquiredfrom],
[mcala_lastapptdate],
[address2_postalcode],
[websiteurl],
[lastonholdtime],
[telephone3],
[address2_line3],
[__FK_value] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value]);

STORE * FROM [RestConnectorMasterTable] into qvd\Accounts365.QVD;
;

//[root]:
//LOAD [@odata.context],
// [__KEY_root]
//RESIDENT RestConnectorMasterTable
// WHERE NOT IsNull([__KEY_root]);

DROP TABLEs RestConnectorMasterTable, Account;

Exit SCRIPT;

Re: Looping through table using REST API

Anonymous — Wed, 21 Nov 2018 20:17:43 GMT

Hi Thom,
You need to put your Do While Loop below the CUSTOM CONNECT statement. Qlikview does not allow Custom Connect statements simulateneously.
So, you just have to change the URL with each loop counter like this

DO while LEN(vURL) > 0	
RestConnectorMasterTable:
SQL SELECT 
	"@odata.context",
    "@odata.nextLink",
	"__KEY_root",
   (SELECT 
		"@odata.etag",
		"accountid",
		"__FK_value"
	FROM "value" FK "__FK_value")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (Url "$(vURL)");

 
[Accounts]:
LOAD [@odata.etag],
	 [accountid],
	 [__FK_value] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
    WHERE NOT IsNull([__FK_value]);

STORE * FROM [RestConnectorMasterTable] into qvd\Accounts365.QVD 
;

[root]:
LOAD	[@odata.context],
	[__KEY_root]
RESIDENT RestConnectorMasterTable
   WHERE NOT IsNull([__KEY_root]);
   
Let vURL = FieldValue('@odata.nextLink',1);



Loop

DROP TABLE RestConnectorMasterTable;

Re: Looping through table using REST API

marksouzacosta — Sat, 15 Jun 2024 22:11:49 GMT

As an addition to this topic, I'm excited to share my latest tutorial on integrating Asana with Qlik Cloud using REST APIs!

In this step-by-step guide, you'll learn:
- How to use Asana REST APIs with Postman
- Creating REST Data Connections in Qlik Cloud
- Extracting data from Asana and saving as Parquet files
- Enhancing connections with custom parameters and pagination

Check out the full video on YouTube:
Integrate Asana with Qlik Cloud Using REST APIs: A Step-by-Step Guide
https://youtu.be/lFwar30BNkQ?si=UZoM_x9L5amdJSnf

Regards,

Mark Costa

topic Looping through table using REST API in QlikView

Looping through table using REST API

Re: Looping through table using REST API

Re: Looping through table using REST API

Re: Looping through table using REST API

Re: Looping through table using REST API