topic Re: Rest connector - loop? in QlikView

Rest connector - loop?

robert_mika — Mon, 11 Jan 2016 08:56:40 GMT

I have unusual situation.

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

The Dovico API

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.

Re: Rest connector - loop?

achiever_ajay — Wed, 06 Apr 2016 03:11:31 GMT

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)&toIndex=$(j)';

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

"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

Re: Rest connector - loop?

prees959 — Mon, 04 Sep 2017 12:49:53 GMT

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

Re: Rest connector - loop?

Anonymous — Wed, 25 Oct 2017 22:28:01 GMT

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?

Re: Rest connector - loop?

Anonymous — Wed, 28 Feb 2018 14:31:05 GMT

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

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

Re: Rest connector - loop?

pawwy1415 — Thu, 29 Nov 2018 12:02:16 GMT

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