Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

Highlighted
reddwarfcrew
New Contributor II

Script Rest API Where Clauses on JSON SQL

Hi, I have a Rest API connection which creates a SQL statement from a JSON string followed by the LOAD statements.

Now I want to exclude certain data from the data set.  I know how to do this in the load statements, but I need to put the same where clause on many loads statements and would prefer to put it just once on the SQL statement.

I can get the following to work:

 

SQL SELECT

     "id" as "id"

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

     WITH CONNECTION(

          QUERY "status" "Obsolete");

 

But what I need to do is exclude where the status is NOT Obsolete as opposed to = Obsolete.

I've tried loads of ways of introducing 'NOT' but all fail.  So how do I do a NOT where clause.

Additionally I want many conditions. Ultimately:

where 'status' is not 'Obsolete' and 'status' is not 'Invalid' and 'area' is not like '*test*'.

Any ideas to save me having to repeat the where clause on my many LOAD statements?

Thanks

1 Reply
MVP
MVP

Re: Script Rest API Where Clauses on JSON SQL

The SELECT statement of the Qlik REST Connector is very limited and has no WHERE clause at all. When using the WITH CONNECTION clause you can specify a QUERY which is named after "Query Parameters" for a HTTP Request which has nothing to do with SQL. So it is the REST API you are connecting to that determine whether you can do any kind of filtering with the help of any query parameter and the syntax is entirely determined by this API and not by Qlik REST Connector.

 

Which REST API are you connecting to? Have you checked the API's documentation? What does it say about filtering what you want to get?

Community Browser