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"
}
}
},
