topic Re: How to apply filter on JSON data with SQL query in Qlik Sense in Connectivity & Data Prep

How to apply filter on JSON data with SQL query in Qlik Sense

Akash_Pohare — Fri, 27 May 2022 13:39:08 GMT

Hi Team,

I have a REST API configured to Service Now and wanted to apply filters on SQL statement itself rather than Load Statement which is generated along with SQL Queries.

Here is the screenshot from data load editor.

Could you please suggest, how can it be achived?

Thanks.

Re: How to apply filter on JSON data with SQL query in Qlik Sense

Ruhulessin — Thu, 15 Sep 2022 11:11:18 GMT

Hi @Akash_Pohare,

Put a where clause after the from table.., like this:

FROM JSON (wrap off) "result"
WHERE "resolved_by" = 'Value'
;

Re: How to apply filter on JSON data with SQL query in Qlik Sense

Akash_Pohare — Tue, 20 Sep 2022 11:23:42 GMT

Hi @Ruhulessin ,

We can't use directly where clause in JSON SQL query statement.

I found this on the help page stating that we have to use "WITH CONNECTION(Parameter with value)" here.

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Create-REST-connection/Create-REST-connection.htm .

I am still trying to filter data based on the date range directly on SQL query but seems it's not possible.

Thanks.