Qlik Community

QlikView Documents

QlikView documentation and resources.

Read about the latest Qlik Community enhancements on the Community News blog!

REST Connector & Pagination on Pipedrive


REST Connector & Pagination on Pipedrive

This document explains some issues identified in testing the REST connector with Pipedrive data using the Start & Limit parameters for paging the output results from the Pipedrive API.

I have been using the REST Connector to connect to Pipedrive data using the information in the REST Connector documentation and from the Pipedrive API site.  Pipedrive API v1 API Documentation
This proved to be challenging to get pagination working consistently.

Looking at the Pipedrive API, a single query returns a maximum of 500 rows. To get more data, then the data must be requested in separate queries, each returning 500 rows. This paging is enabled by setting a "start" and "limit" parameter in the api call.

Whilst this appears straightforward according to the Pipedrive documentation, when this is applied to the Qlik REST connector it becomes a bit more fun.

The pagination settings in the REST connector seem to prefer the information for the page settings to be in the header of the returned data. The issue with pipedrive is that the pagination table is in the footer of the results. This can cause the SELECT option for the REST connector to completely miss the additional tables unless a small value for the limit parameter is used.

The API call I have been using is https://api.pipedrive.com/v1/deals?pretty_output=1&start=0&limit=500&api_token=**********

to return the first 500 deals. You can use this in Chrome to preview the results.
The "pretty_output=1" option just formats the data in the browser.

At the end of the results set is the additional data pagination table showing the start, limit and the next_start value, as well as other tables under related objects

















If this same query is used to create a REST connection, the subsequent select option below connect only offers the data tables in the results, as shown below.


If instead the limit is set to a low value, say 5 instead of 500, then the data, pagination & related objects are all visible under root.


When testing both of these queries in Chrome (IE does not display JSON results by default), the pagination tables and related_objects are included, but these tables only appear to be read by the select statement that builds the object & fields list if in they are not too far down the result set.

In testing we found that setting the limit at 19 records or less would show the additional data, but using a 20 record limit or higher, the additional data tables would not be displayed in the Objects & Fields list!

Once the select statements have been created in the wizard against the small limit value, the connect statement can be edited to increase the limit to the maximum value of 500 and the script will execute successfully, or you can replace the fixed values with variables in a Do..Until loop to load all data rows.

This has been tested on both QlikView 11.2 SR15 and v12 SR4 using the REST Connector v1.0.

This may explain some of the questions relating to the REST connector and pagination that have been posted on the community.

Colin Albert, K3FDS.

Labels (1)

Use custom pagination in Qlik Sense. The following code worked for me:

LIB CONNECT TO 'Pipedrive Deals';

Let start = 0;

Let limit = 500;



          SQL SELECT



                  "id" AS "id_u1",




                  "value" AS "value_u2",











































              FROM "data" PK "__KEY_data" FK "__FK_data"),










                  FROM "pagination" FK "__FK_pagination")

              FROM "additional_data" PK "__KEY_additional_data" FK "__FK_additional_data")

          FROM JSON (wrap on) "root" PK "__KEY_root"

          WITH CONNECTION(QUERY "start" "$(start)");

    start = start + limit;




    [start] AS [start],

        [limit] AS [limit],

        [more_items_in_collection] AS [more_items_in_collection],

        [next_start] AS [next_start]

    RESIDENT RestConnectorMasterTable

    WHERE NOT IsNull([__FK_pagination]);


let vLoop = Peek('more_items_in_collection');


LOOP WHILE vLoop = 'True';


LOAD [id_u1] AS [id_u1],

[person_id] AS [person_id],

[stage_id] AS [stage_id],

[title] AS [title],

[value_u2] AS [value_u2],

[currency] AS [currency],

[add_time] AS [add_time],

[update_time] AS [update_time],

[stage_change_time] AS [stage_change_time],

[active] AS [active],

[deleted] AS [deleted],

[status] AS [status],

[next_activity_date] AS [next_activity_date],

[next_activity_time] AS [next_activity_time],

[next_activity_id] AS [next_activity_id],

[last_activity_id] AS [last_activity_id],

[last_activity_date] AS [last_activity_date],

[lost_reason] AS [lost_reason],

[visible_to] AS [visible_to],

[close_time] AS [close_time],

[pipeline_id] AS [pipeline_id],

[won_time] AS [won_time],

[first_won_time] AS [first_won_time],

[lost_time] AS [lost_time],

[products_count] AS [products_count],

[files_count] AS [files_count],

[notes_count] AS [notes_count],

[followers_count] AS [followers_count],

[email_messages_count] AS [email_messages_count],

[activities_count] AS [activities_count],

[done_activities_count] AS [done_activities_count],

[undone_activities_count] AS [undone_activities_count],

[reference_activities_count] AS [reference_activities_count],

[participants_count] AS [participants_count],

[expected_close_date] AS [expected_close_date],

[last_incoming_mail_time] AS [last_incoming_mail_time],

[last_outgoing_mail_time] AS [last_outgoing_mail_time],

[stage_order_nr] AS [stage_order_nr],

[person_name] AS [person_name],

[org_name] AS [org_name],

[next_activity_subject] AS [next_activity_subject],

[next_activity_type] AS [next_activity_type],

[next_activity_duration] AS [next_activity_duration],

[next_activity_note] AS [next_activity_note],

[owner_name] AS [owner_name],

[__KEY_data] AS [__KEY_data],

[__FK_data] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_data]);

DROP TABLE RestConnectorMasterTable;


Hi Jacques,

I used your script and it loads successfully but only pulls a limited amount of data. However, as I decrease the limit, I can pull more data. Setting the limit to 1 pulls the most amount of data but I am not entirely sure if this is the whole data set. Can you offer some insight and perhaps any optimizations that you have had recently. Much appreciated.



Hi George,

I have not worked with this since the post and it is possible that they have made changes to the API. It sounds like a pagination issue though.

Version history
Revision #:
1 of 1
Last update:
‎2016-08-09 03:12 PM
Updated by: