Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

Re: REST - queryHeaders as variable?

Petter:

I was able to follow your logic with help from Qlik's Architect, but now I have another issue:

The loop goes through all states, but the output is only one state:01 that is specified in the connection string

1. Connection:

'ABC'

(https://api.census.gov/data/2015/acs5?get=NAME,GEOID&for=school+district+(unified):*&in=state:01&key...eyxxx)

2. Script:

for i= 01 TO 56

paramURL = 'https://api.census.gov/data/2015/acs5?get%2NAME,GEOID&for%2school+district+(unified):*&in%2state:$(i...mykeyxxx;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=None;XUserId=xxx;XPassword=xxx;';

LIB CONNECT TO 'ABC';

RestConnectorMasterTable:

SQL SELECT

  "__KEY_root",

  (SELECT

  "@Value",

  "__FK_root"

  FROM "root" FK "__FK_root" ArrayValueAlias "@Value")

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

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

WITH CONNECTION (

URL "$(paramURL)"

);

[root]:

LOAD [@Value] AS [@Value],

  [__FK_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_root]);

next

DROP TABLE RestConnectorMasterTable;

Partner
Partner

Re: REST - queryHeaders as variable?

Petter:

I have figured out what was the issue. I was mixing CUSTOM CONNECT TO with WITH CONNECTION

Working Script:

LIB CONNECT TO 'ABC';

let paramURL = 'https://api.census.gov/data/2015/acs5?get=NAME,GEOID&for=school+district+(unified):*&in=state:$(i)&k...

RestConnectorMasterTable:

SQL SELECT

  "__KEY_root",

  (SELECT

  "@Value",

  "__FK_root"

  FROM "root" FK "__FK_root" ArrayValueAlias "@Value")

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

WITH CONNECTION (

URL "$(paramURL)"

);

[root]:

LOAD

  [@Value] AS [@Value],

  [__FK_root] AS [__KEY_root]

   

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_root]);

DROP TABLE RestConnectorMasterTable;

next

Highlighted
prees959
Contributor II

Re: REST - queryHeaders as variable?

Hi,

It it possible to store the Authorization token inside a variable that.  I need to run the API inside a loop

and on each pass update the authorization token..

eg

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://.mydomain/;timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWind...  'headers'%1Authorization%2Token token%%$(APIToken);PaginationType=None;XUserId=xxx;XPassword=99999;";

Is this possible?

Many thanks for any advice!

Phil

prees959
Contributor II

Re: REST - queryHeaders as variable?

Hi Petter,,

The API i have been told to use will only bring back 100 records at a time.  I have been told to use 'Limit' and 'Offset' in my query parameters - nut cannot get this to work.  Do I need any more information from the developers for this?

Thanks for any advice,

Phil

pawwy1415
Contributor II

Re: REST - queryHeaders as variable?

Hi  ,
I am looking for the solution to filter the data from rest connector.
I have a query like below
SQL SELECT
     "id" as "id",
    "name"  as "name_u1"
FROM JSON (wrap on) "root" PK "__PK_root";
 
From name field I have to filter only Approved Data i.e., name= 'Approved'.
Could you please help me to write the condition on API. I have tried query Parameters but no luck.
Thanks in advance.