Filtering Data in Rest Connector Request Body

[sourabhverma107]

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  

[stevedark]

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]

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]

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]

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]

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]

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 · Accepted solution]

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