Qlik Community

Ask a Question

QlikView Connectivity

Discussion board for collaboration related to QlikView Connectors.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
sourabhverma107
Contributor III
Contributor III

Filtering Data in Rest Connector Request Body

Hi Team,

I am facing issue in applying filter in while fetching data from Rest API.

I am using POST method to fetch the data and providing filter like below in requester body which is suggested by the API team.

{ "Filters": [{"Columnname":"Date Modified","Values":["21/06/2020"]}]}

When i am trying above syntax i am getting an error like below:

Parameter "]}" has no value or a missplaced semicolon

Could anyone help me on how to apply filter in Request body of Post method.

Thanks
Sourabh

@stevedark  

1 Solution

Accepted Solutions
sourabhverma107
Contributor III
Contributor III
Author

Hi steve,

 

I got the solution, below is the article which help me to solve the issue:

https://support.qlik.com/articles/000048248

 

Thanks,
Sourabh

View solution in original post

7 Replies
stevedark
MVP
MVP

Hi @sourabhverma107 

The syntax of the query is entirely dependant on the API. It would be helpful if you could advise which API you are sending the request to, give a link to their API documentation and share your source code.

It may be that the close of square bracket is being treated as part of the Qlik statement rather than being passed to the API.

The best way to construct and test REST queries is to use Postman, once you have it working in there you can work on bringing it back to Qlik. Get that here: https://www.postman.com/

Steve

sourabhverma107
Contributor III
Contributor III
Author

HI Steve,

 

Thank you for the response.

 

API is from Process Unity.

So should i remove the square bracket at the end and pass like below:

{ "Filters": {"Columnname":"Date Modified","Values":["21/06/2020"]}}

Postman is blocked in my Org so trying directly on Qlik.

 

Thanks

stevedark
MVP
MVP

Often square brackets are used as placeholders in API documentation to show optional content, so it may be they are not required - but if the API expects them you can not just leave them out.

Can you please post a link to the API documentation and the whole LOAD statement, this will help me spot the issue.

Also, I would recommend trying to use Postman another way, perhaps via your mobile data?

Steve

sourabhverma107
Contributor III
Contributor III
Author

Yes if i am removing the filter then its not giving an error but its giving complete data without filtration


As postman is blocked in my organization so i tried in insomnia tool and its working fine there:
{ "Filters": [{"Columnname":"Date Modified","Values":["21/06/2020"]}]}


We do not have documentation from the API team, further you can find the load script below :


Custom connect to "Provider=QVRestConnector.exe;url="URL";timeout=30;method=POST;requestbody=%3Filters%3:[{%3Date Modified%3,%3values%3: [%309/02/2002%3];]};
(Rest of the things in connection string is headers)

Restconnectormastertable:
SQL Select
"Message",
"Has Error"
"_Key_Root"
(SELECT
"Field1",
"Field2",
"Date Modified",
"_FK_Data"
From "Data" FK "__FK_Data")
From JSON(wrap on) "root" pk "__Key_root"


Error Received: Parameter "]}" has no value or a misplaced semicolon

stevedark
MVP
MVP

Hi @sourabhverma107 

This is not where I would expect the request body to appear. I presume that you are using QlikView rather than Sense?

When you create the connection using the wizard is this the connection string that is returned, or have you modified that afterwards?

Typically I would put the body into the query with the WITH CONNECTION keyword, as this is more flexible.

For instance, this is code for getting a token from Twitter. The connector points to a generic location (https://jsonplaceholder.typicode.com/posts) and then all other settings are overwritten in code:

 

    tmpBearerKey:
    SQL SELECT
		"tokem_type",
        "access_token"
    FROM JSON (wrap on) "root" PK "__KEY_root"
	WITH CONNECTION (  
      URL "https://api.twitter.com/oauth2/token",
      HTTPHEADER "content-type" "application/x-www-form-urlencoded",
      HTTPHEADER "Authorization" "Basic $(vBASE64Key)",
      QUERY "grant_type" "client_credentials"
    )
;

 

The syntax for request body will probably be a bit different than for HEADER and QUERY entries, but it is likely to be similar. Doing a Google for WITH CONNECTION should point you in the right direction.

Steve

sourabhverma107
Contributor III
Contributor III
Author

Hi Steve,

Yes i am using qlikview, and token is already generated. Right now i want to filter out the data from a API table for Date Modified field.

Yes connection string appears once details in wizard completes. 
I am not making any changes in the connection string, manually passing the token in the wizard, just for your reference i have put the variable in connection string.

Sure thanks i will check on the With connection.

Thanks,
Sourabh

sourabhverma107
Contributor III
Contributor III
Author

Hi steve,

 

I got the solution, below is the article which help me to solve the issue:

https://support.qlik.com/articles/000048248

 

Thanks,
Sourabh

View solution in original post