Rest connector - loop?

[robert_mika]

I have unusual situation.

I need to assign a project number to a table I'm going to retrieve:

The Dovico API

(DOVICOhostedAPI - Projects)

is allowing to assign only one link for a connection.

I'm doing this using variable the picks part of the string from table(projectNo)  and adding this variable to a connection

For  vFileNo = 1 to NoOfRows('ProjectNo');

      Let vProjectNo = Peek('ProjectID',vFileNo-1,'ProjectNo');

      Let vFileName=   'https://api.dovico.com/Projects/$(vProjectNo)/Statistics/?version=5';

CUSTOM CONNECT TO  "Provider=QvRestConnector.exe;url=$(vFileName);

timeout=180;method=GET;autoDetectResponseType=true;

keyGenerationStrategy=3;useWindowsAuthentication=false;useCertificate=No;

certificateStoreLocation=CurrentUser;certificateStoreName=My;queryParameters=version%25;

queryHeaders=Authorization%2WRAP access_token%%2%%3client%%2336b434e68de4f97ba5ad1608fc802b0.26395&user_token%%2db2b40ac154c4dbeb36f5e686899b211.26395%%3%1Accept%2application/json;

PaginationType=None;

XUserId=BIZYJZVNJbaCGLA;

XPassword=cJfbZSUEQLYGXZdOPTcIWSIGDCbSWWJMFTcSEXVKLLJCTaB;"

then my SQL statement

SQL SELECT

         (SELECT

               "Actual",              

               "Budget"

          FROM "Hours" FK "__FK_Hours")

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

next vFileNo

so I can get all the data.

What I'm struggling with is to add the ProjectID to  the table from the API

so my outcome will look like this

ProjectID,Actual,Budget

How to loop thru all links, getting data and assign particular project to one line only.

[achiever_ajay]

Dear Robert,

Greetings.

I am using REST Connector for "ZOHO CRM & Recruit" to fetch data from their API. Their API has limitation of 20 record fetch : which i overcome using QlikView FOR LOOP & passing Loop No. Hope below script should be useful to your needs.

CODE:

Let k=0;

For i=0 to 1000000

LET vToken='xxxxxx';

Let k=$(k)+1;

LET j=$(i)+19;

LET vLastValue=FieldValueCount('[@Content]');

LET vURL='https://recruit.zoho.com/recruit/private/xml/Clients/getRecords?authtoken%2$(vToken)&fromIndex=$(i)&...

TRACE $(i)&'-'&$(j)&'-'&$(vURL);

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=$(vURL)

;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false

;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=None;XUserId=GKCCGYC;XPassword=cYdFdSD;";

RestConnectorMasterTable:

SQL SELECT

  "__KEY_response",

  (SELECT

  (SELECT

  (SELECT

  "attr:no" AS "no",

  "__KEY_row",

  "__FK_row",

  (SELECT

  "attr:val" AS "val",

  "@Content",

  "__FK_FL"

  FROM "FL" FK "__FK_FL" ContentFieldAlias "@Content")

  FROM "row" PK "__KEY_row" FK "__FK_row")

  FROM "Clients" PK "__KEY_Clients" FK "__FK_Clients")

  FROM "result" PK "__KEY_result" FK "__FK_result")

FROM XML "response" PK "__KEY_response";

[FL]:

Data_Values:

LOAD RowNo() as Rno, '$(i)'&'-'&'$(j)' as %LoopNo, '$(k)' AS Seq,*;

LOAD [val],

  [@Content],

  [__FK_FL] AS [__KEY_row]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_FL]);

DROP TABLE RestConnectorMasterTable;

LET i=$(j);

LET vCurrentLastValue=FieldValueCount('[@Content]');

IF ($(vCurrentLastValue)=$(vLastValue)) THEN

  EXIT FOR;

ENDIF

NEXT i

STORE Data_Values into Data_Values.qvd(qvd);

Regards,

Ajay Kumar

Singapore

+65-9390 2241

[prees959]

Hi Ajay,

This seems exactly what I need to accomplish.  The API I am using is limited to 100 records at a time and have been told by the developers that I need to use Limit and Offset in my query parameters.  Can you advise on how to loop through the connection to retrieve all records please?

Phil

[Anonymous]

Your answer brings up more questions for me as a noob.

So how do you parse the result from the REST call?

SQL SELECT

         (SELECT

               "Actual",             

               "Budget"

          FROM "Hours" FK "__FK_Hours")

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

Totally don’t understand how this works. Is there a link to some docs that explains how to parse json results?

[Anonymous]

Also looking to find a good solution for parsing JSON results.

Need to loop through records because of limitations in the queries.

[pawwy1415]

Hi Everyone,

We are trying to retrieve the data from  SharePoint 2013  using Rest connector in Qliksense.

Due to Share point limit we were not able to extract the complete data.

When we tried with "/Items?$Top=300000" in the URL its fetching records up to 21000 but still some dates are missing.

By removing Top=300000 by passing any field here fetching only 100 records.

Could you please let me know how to create the Custom connection for Rest Connector in the qliksense to try with this loop method.

Thanks in advance