Skip to main content

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
reddwarfcrew
Contributor II
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

3 Replies
petter
Partner - Champion III
Partner - Champion III

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?

Josh-Head-of-BI-HGreg
Contributor II
Contributor II

Why can't we just filter the data in a View in Qlik and move on, as in any other BI tool?
Why do we just not have Filter option in the Data Manager and all other options?

DavidFosterVF
Creator
Creator

The QRS App api has a 'filter' parameter. 

Open API specification for Repository Main API − get /app - Qlik | Help

How would I include this in the WITH CONNECTION section?