2 Replies Latest reply: May 24, 2018 9:36 AM by Pascal Roobrouck RSS

    Skip and Take dynamic from JSON load

    Paul Damen

      All,

       

      I have a JSON load from AFAS which I use the Qlik Sense rest connector for. Problem I face is that I can't get the whole pagination to work. I have a Skip and Take in the load which gets the value 0 and 100 from the source.

       

      What I need is to make this skip dynamic. So 0 in the first load, and then add 1000 for example in a loop. How can I get this to work? Not real familiar with this JSON type of scripting.

       

      Regards, Paul

       

      Let total = 0;

      Let totalfetched = 0;
      Let startAt = 0;
      Let pageSize = 100;

       

      for startAt = 0 to total step pageSize


      RestConnectorMasterTable:
      SQL SELECT
      "skip",
      "take",
      "__KEY_root",
      (SELECT ....

      .....

      ......

      .....

      .....

      FROM URL.....

        • Re: Skip and Take dynamic from JSON load
          omar bensalem

          As u can see, no one has been able to answer your question; and that's my friend because of the way you explained (did not) your issue.

           

          Can u please put it in different, clearer words?

          • Re: Skip and Take dynamic from JSON load
            Pascal Roobrouck

            I have the same problem. Let me try to describe it in more detail so Qlik people can take a look at it.

             

            1. When retrieving data from Afas Profit (an ERP system, made by the Dutch company Afas), you can use the Qlik REST connector. The AFAS systemprovides REST services as a way to extract data from their system to other applications.

             

            2. I managed to configure the Qlik REST Connector to retrieve records from Afas. I did it both in Qlikview and in QlikSense Server.

             

            3. The problem is 'pagination', ie. when you want to retrieve a very large set of data (eg > 1 million records), maybe you do not want to retrieve them all in 1 REST request. This could result in network load, HTTP timeouts etc. so you do it in chunks, telling the REST service to retrieve the first set of (eg) 1000 records, then the next set of 1000 (record 1001 - 2000) and so on.

             

            Qlik REST Connector provides a number of ways to do this pagination, but the way AFAS wants it is different from the avaialble options. (again, as far as I know - maybe there is a solution I don't know yet..). Pagination types supported by Qlik are 'None, Offset, Next Page, Next Token, Next URL, Custom'

             

            The way that AFAS wants it is to add to the REST Connector 2 'query Parameters' : 'skip'and 'take'

            skip determines how many records to skip, and take is the number of records to return. So with skip 5000 take 1000 you would return records 5001 - 6000

             

            Here is an example retrieving the first 20000 records.

             

            Page15-31-02.PNG

            The problem with this approach is that now skip and take are hard-coded in the Connector-configuration..

            what we need is :

            1. a way to determine at run-time which records will be retrieved...

            or

            2. a way to retrieve them all, but have the REST services do the transfer in reasonable chunks, not all records in one request..

             

            Thanks.