Qlik Community

Qlik Sense Data Connectivity

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

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Optional SQL Select with REST Data Connection

Hey there,

 

so I am developing some Dashboards for a many customers.

I know the maximum or all possible fields. So it's a REST Connection, and they get a Response with just the fields that are existing.

My Question now:
How can I develop the Script so, that if there ist every possible field in the REST Response, everythings fine, and if there are a lot of fields missing, how will the script still continue to work?

 

Thanks in advance!

1 Solution

Accepted Solutions
Highlighted
Support (Former)
Support (Former)

Re: Optional SQL Select with REST Data Connection

Hi, 

SQL statement of REST connector does not throw any error if a filed does not exist in the query. It simply skips non-existing fields.

The auto-generated LOAD statement following the SQL query (if  you create query from the Select Data wizard) however, may throw errors if the field does not exist. You can overcome this by replacing LOAD [fieldNames] with LOAD * . Or simply remove the LOAD statement.

For example, the following piece of script loads data from https://jsonplaceholder.typicode.com/posts, including field "nonexisting" that is not in the API source. 

 

LIB CONNECT TO 'testJSON';

RestConnectorMasterTable:
SQL SELECT 
	"userId",
	"id",
	"title",
	"body",
        "nonexisting" //this field does not exist in the source
FROM JSON (wrap on) "root";

[root]:
NoConcatenate LOAD	*
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

 

 

Vu Nguyen
If a post helps resolve your issue, please mark the answer as correct.

View solution in original post

1 Reply
Highlighted
Support (Former)
Support (Former)

Re: Optional SQL Select with REST Data Connection

Hi, 

SQL statement of REST connector does not throw any error if a filed does not exist in the query. It simply skips non-existing fields.

The auto-generated LOAD statement following the SQL query (if  you create query from the Select Data wizard) however, may throw errors if the field does not exist. You can overcome this by replacing LOAD [fieldNames] with LOAD * . Or simply remove the LOAD statement.

For example, the following piece of script loads data from https://jsonplaceholder.typicode.com/posts, including field "nonexisting" that is not in the API source. 

 

LIB CONNECT TO 'testJSON';

RestConnectorMasterTable:
SQL SELECT 
	"userId",
	"id",
	"title",
	"body",
        "nonexisting" //this field does not exist in the source
FROM JSON (wrap on) "root";

[root]:
NoConcatenate LOAD	*
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

 

 

Vu Nguyen
If a post helps resolve your issue, please mark the answer as correct.

View solution in original post