4 Replies Latest reply: Feb 8, 2017 3:00 PM by Priya Sampathkumar RSS

    Qlik Rest Connector pagination problem

    Jonathan Alm

      Hi,

      I am trying to go through a series of records to be able to fetch all the data.

      Today I do this by looping through all records, but since I received the user guide I see that this could be solved through pagination.

       

      However I fail when I try to implement this.

       

      The URL is setup like this:

      http://aaa.bbb.local:8020/api/platform/person/v1/persons/?_offset=0

       

      The parameter is "?_offset"

      The call retrieves 1000 rows.

      To get 1000->2000 I enter

      http://aaa.bbb.local:8020/api/platform/person/v1/persons/?_offset=1000

       

      If I enter this into chrome Rest Api Client I get the following:

      - "page": [1000]

      0: 

      {

       

      "id": "2335AA2-212-2AA"

      "civicNo": "1234567"

      "firstName": "Carl"

      "lastName": "Smith"

      "modifiedTime": 1428919220291

      }

      1: 

      {

      "id": "2325FG2-222-313"

      "civicNo": "1234321"

      "firstName": "Marco"

      "lastName": "Polo"

      "modifiedTime": 1429004337935

      }

       

      "queryTotal": 3502

      "pageSize": 1000

      }

       

      page: [1000] means that there are 1000 posts fetched

      queryTotal means that there are 3502 rows in total

      pageSize is unclear. Both page and pageSize are always set to 1000.

       

      If I enter the URL in QLIK I get:

      RestConnectorMasterTable:

      SQL SELECT

        "id",

        "civicNo",

        "firstName",

        "lastName",

        "modifiedTime"

      FROM JSON (wrap off) "page";

       

       

       

      [page]:

      LOAD

        [id],

        [civicNo],

          [firstName],

          [lastName],

        [modifiedTime]

      RESIDENT RestConnectorMasterTable;

       

       

       

       

       

      DROP TABLE RestConnectorMasterTable;

       

      So my question is:

      How do I set up the pagination to be able to fetch the following 3502 rows, which means that it should iterate 4 times to fetch all?

       

      Kind regards, Jonathan

       

        • Re: Qlik Rest Connector pagination problem
          Petter Skjolden

          If you select Pagination of Offset which is what your REST source demands, the connector also needs information about where to find the total number of rows to fetch. That information can be returned either in the Response Header or in the data itself. That varies with various sources and the way they have chosen to implement their RESTful API.

           

          You need to fill out both the Query Parameter with the Offset and also specify it in the section for Pagination.

           

          So it should look something like this:

           

          2016-01-27 #1.PNG

           

          Maybe the "Total field path" needs to be adjusted ... but I think it should be right...

            • Re: Qlik Rest Connector pagination problem
              Jonathan Alm

              Hi,

              thanks for your help. I have tried your solution, but I still only get the first 1000 posts.

               

              I will see if I can get hold of the developer of the API and ask that person what is missing. Maybe there is something with the API that does not support the call.

               

              I will let you know what I come up with.

               

              Kind regards, Jonathan

                • Re: Qlik Rest Connector pagination problem
                  Petter Skjolden

                  Looking closer at your JSON result data from Chrome I can see that you actually get the necessary information that the Qlik REST Connector needs to know the total number of rows to get. But you don't retrieve this information as part of the SQL SELECT statement. Then there is no way for the REST-connector to know how it will reach the end of data and it will refuse to iterate. Unfortunately it does not send any warning or error message to that effect.

                   

                  You should retrieve on table with your real data and one table which contains queryTotal and pageSize. Then you will be able to in the REST Connector dialog box to enter the queryTotal. But just entering it in the dialog box without retrieving the data and not specifying the right path to the data will not make it work.

                   

                  It is a bit hard to give you exact specification on exactly what to enter where when I don't see the screenshots and preview that you are getting when you do your connect. Is it possible for you to share that with us?