ZOHO API with Qlik REST Connector and table transpose

Anonymous — Mon, 09 May 2016 06:27:29 GMT

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.

Let toIndex = (fromIndex + pageSize - 1);

//trace "fromIndex: " $(fromIndex);

//trace "toIndex: " $(toIndex);

RestConnectorMasterTable:

SQL SELECT

"__KEY_response",

(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

Re: ZOHO API with Qlik REST Connector and table transpose

Anonymous — Wed, 19 Oct 2016 05:56:10 GMT

Update for Qlik Sense 3.1

My script started failing around October 2016 when we upgraded the server to QS 3.1. It still worked on my 3.0 desktop so I don't know what Qlik changed. On the QS 3.1 server it was doing a cartesian join while looping through Tablename. This was caused by the REST API call returning duplicate record keys where there were two opportunities (Potentials) for the same Account. The effect was that instead of 100 records, it was generating 100 million records which took 2 hours and caused the server to slow down. MY QS dashboard showed over $1 trillion in likely sales which would be great if it were real 🙂

When we changed the Key generation strategy in the REST connector from SequenceID to Fully qualified record, this problem went away.

Please mark this as "Helpful" so I get some points - it took a lot of work.

Re: ZOHO API with Qlik REST Connector and table transpose

guruprem — Tue, 29 Nov 2016 05:47:07 GMT

Hi Tim,

I followed the steps but I got error 4600, "unable to process your request" from zoho. Could you advise know what could have gone wrong?

Re: ZOHO API with Qlik REST Connector and table transpose

hagay — Sat, 29 Dec 2018 23:12:09 GMT

Hi,
First of all I wanted to say thank you very much for the code, it helped me a lot.
I was able to set up and get the correct data for Potentials module.
But for some reason I tried to define the same thing as the contacts module but I can not,
Can you help me - which code lines need to be changed?
Of course, I changed the API URL, etc.

thank u

topic Re: ZOHO API with Qlik REST Connector and table transpose in QlikView

ZOHO API with Qlik REST Connector and table transpose

Re: ZOHO API with Qlik REST Connector and table transpose

Re: ZOHO API with Qlik REST Connector and table transpose

Re: ZOHO API with Qlik REST Connector and table transpose