How can I select multiple sub-attributes with a select statement for a REST connection?

mr_vince — Thu, 18 Nov 2021 16:48:08 GMT

Hi there,

I am using Qlik Sense and connecting via Rest Connector to a tool for retrieving employee data.

I am connecting to one endpoint that contains all the information in a very nested way with different levels of sub-attributes. I am interested in getting information in one table about:

Employee value
First name value
Last name value
Email value
Supervisor value
Supervisor name
Supervisor last name
Office name
Department name

The problem I am facing is that I don't know how to select all the sub-attributes within the script. I can only manage to select a single attribute like:

SQL SELECT

"__KEY_root",

(SELECT

"__FK_results",

"__KEY_results",

(SELECT

"id"

(SELECT

"value"

FROM "id")

FROM "attributes" FK "__FK_properties")

FROM "data" PK "__KEY_results" FK "__FK_results")

FROM JSON (wrap on) "root" PK "__KEY_root";

I also can't use the data loader for selecting the tables in the datasource as the connection is token based which throws an error when opening it in the UI. So I don't know about the proper syntax to use here.

Can someone please help?

This is the format of the datasource response I need to select the data from:

{

"success": true,

"data": [

{

"type": "Employee",

"attributes": {

"id": {

"label": "ID",

"value": 1234,

"type": "integer",

"universal_id": "id"

"first_name": {

"label": "First name",

"value": "Employee First Name",

"type": "standard",

"universal_id": "first_name"

"last_name": {

"label": "Last name",

"value": "Employee Last Name",

"type": "standard",

"universal_id": "last_name"

"email": {

"label": "Email",

"value": "Employee Email",

"type": "standard",

"universal_id": "email"

"supervisor": {

"label": "Supervisor",

"value": {

"type": "Employee",

"attributes": {

"id": {

"label": "ID",

"value": 4567,

"type": "integer",

"universal_id": "id"

"first_name": {

"label": "First name",

"value": "Supervisor Name",

"type": "standard",

"universal_id": "first_name"

"last_name": {

"label": "Last name",

"value": "Supervisor Last Name",

"type": "standard",

"universal_id": "last_name"

"email": {

"label": "Email",

"value": "Supervisor Email",

"type": "standard",

"universal_id": "email"

}

"type": "standard",

"universal_id": "supervisor"

"office": {

"label": "Office",

"value": {

"type": "Office",

"attributes": {

"id": 6789,

"name": "USA"

}

"type": "standard",

"universal_id": "office"

"department": {

"label": "Department",

"value": {

"type": "Department",

"attributes": {

"id": 67890,

"name": "Department Name"

}

"type": "standard",

"universal_id": "department"

}

topic How can I select multiple sub-attributes with a select statement for a REST connection? in Connectivity & Data Prep

How can I select multiple sub-attributes with a select statement for a REST connection?