Skip to main content
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
Showing results for 
Search instead for 
Did you mean: 
Not applicable

ZOHO API with Qlik REST Connector and table transpose

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


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);









                      "attr:val" AS "val",



                  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


      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


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



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


LOAD Distinct KEY_row Resident FL;

FOR i = 0 to NoOfTables()


  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'Table2.*');


FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];


Drop Tables FL, TableList;

More links

Zoho CRM API | Developer Help - Zoho CRM

REST Connector

QlikView - Using the REST Connector

The Generic Load

3 Replies
Not applicable

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.

Partner - Creator III
Partner - Creator III

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?

Contributor II
Contributor II

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