I struggled with this for a while so I thought I'd share how to get data from the Zoho API
1. Check that your ZOHO API works
Create your token and test the ZOHO API. Start with a simple URL in a browser to make sure that your token works. Google Zoho API for help on this.
2. Install / Configure the Qlik REST Connector
Download and install the REST Connector from Qlik Market. Currently here: REST Connector
In Sense, Create new connection. (Note, I'm using the ZOHO API method that references a custom view but you can adapt this for other API methods. A custom view lets me filter the records and choose the fields that I want.)
Rest Connector Parameters
URL: https://crm.zoho.com/crm/private/xml/Potentials/getCVRecords
Method: Get
Query Parameters:
scope: crmapi
cvName: The name of your custom view - in my case it's: All Open Opportunities
authtoken: 1234abcd... (put in your real token so you can use the "Test Connection" button
newFormat: 1 or 2 (depends if you want null fields with the text "null" in them)
fromIndex: 1
toIndex: 1 (default to one record for testing - we will update this from the Qlik Script)
Now Test Connection. Should return "Test was successful"
Save this with a name to use in Qlik, in my example it is "Zoho Opportunities"
3. Create the Qlik Script
Start by connecting to the connection that you created in part 2 and generate a script automatically. That is the heart of this code. You should test that and then add the loops and transpose from this code.
Here's the full Qlik Script with some explanation
Let pageSize = 20; // how many records in one call - try 100 for efficiency
Let fromIndex = 1;
Let vTableRows = 0; // how many rows fetched so far, each record may be 35 rows of fields
Let vAuthToken = '6c5f0f384e8b3eb5a7c890e7'; // not my real token - use your own
LIB CONNECT TO 'Zoho Opportunities'; // match to the connection name created in 2.
DO
Let toIndex = (fromIndex + pageSize - 1);
//trace "fromIndex: " $(fromIndex);
//trace "toIndex: " $(toIndex);
RestConnectorMasterTable:
SQL SELECT
"__KEY_response",
(SELECT
(SELECT
(SELECT
(SELECT
"attr:val" AS "val",
"@Content",
"__FK_FL"
FROM "FL" FK "__FK_FL" ContentFieldAlias "@Content")
FROM "row" PK "__KEY_row" FK "__FK_row")
FROM "Potentials" PK "__KEY_Potentials" FK "__FK_Potentials")
FROM "result" PK "__KEY_result" FK "__FK_result")
FROM XML "response" PK "__KEY_response"
// the script above was generated automatically from the data. It's a bit different if you use JSON instead of CSV
// I just took out the closing ; and added the next bit.
// Pass some parameters to the REST Connector to overwrite the Query Parameters
// You can pass the URL if you want the connection to be more generic.
// See the documentation on the REST connector - search qlik rest connection help
WITH CONNECTION (
QUERY "fromIndex" "$(fromIndex)",
QUERY "toIndex" "$(toIndex)",
QUERY "authtoken" "$(vAuthToken)"
);
// Need to check if we fetched more than one row
Let rowsFetched = NoOfRows('RestConnectorMasterTable') - vTableRows;
//trace "rowsFetched: " $(rowsFetched);
Let vTableRows = vTableRows + rowsFetched;
Let fromIndex = fromIndex + pageSize;
LOOP while rowsFetched > 1;
// use >1 because even once we run out of data we still get 1 row of response
[FL]:
LOAD [__FK_FL] AS KEY_row, val, @Content // Warning, I changed the name of the ___Key_Row field from the auto script.
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_FL]);
DROP TABLE RestConnectorMasterTable;
//FL is a table with a separate row for each field which is useless unless transposed.
// So now we use Generic Load to pull the data out of this horrible structure
//see https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic
Table2:
Generic LOAD *
Resident FL;
// It's OK to use the data as many separate tables - it may be more efficient.
// if you are OK with this, just drop FL and exit here.
// This last bit brings it all toghether into one table
// adapted from https://community.qlik.com/docs/DOC-3020
ResultTable:
LOAD Distinct KEY_row Resident FL;
FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'Table2.*');
NEXT i
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
NEXT i
Drop Tables FL, TableList;
More links
Zoho CRM API | Developer Help - Zoho CRM
REST Connector
QlikView - Using the REST Connector
The Generic Load
