Loading data from nested select statements

[martynwb]

Hi, I'm fairly new to Qlik Sense and I'm having difficulty with a query. I want to pull information from a REST API for an online database (called Kintone). This has been fairly trivial for other queries but is causing issues for me for this particular dataset; each record appears to be a different table.

This is the generated insert query in the QVD gnerator:

LIB CONNECT TO 'KINTONE_RECORDS (emazure_martyn.booth@euromoneyplc.com)';

RestConnectorMasterTable:
SQL SELECT
"__KEY_records",
(SELECT
"type" AS "type_u0",
"value" AS "value_u0",
"__FK_record_number"
FROM "record_number" FK "__FK_record_number"),
(SELECT
"__KEY_access_permissions",
"__FK_access_permissions"
FROM "access_permissions" PK "__KEY_access_permissions" FK "__FK_access_permissions"),
(SELECT
"type" AS "type_u4",
"value" AS "value_u3",
"__FK_number_of_records"
FROM "number_of_records" FK "__FK_number_of_records"),
(SELECT
"__KEY_Attachment",
"__FK_Attachment"
FROM "Attachment" PK "__KEY_Attachment" FK "__FK_Attachment"),
(SELECT
"type" AS "type_u6",
"value" AS "value_u4",
"__FK_project_name"
FROM "project_name" FK "__FK_project_name"),
(SELECT
"__KEY_project_types",
"__FK_project_types"
FROM "project_types" PK "__KEY_project_types" FK "__FK_project_types"),
(SELECT
"type" AS "type_u8",
"value" AS "value_u5",
"__FK_confidential_radio"
FROM "confidential_radio" FK "__FK_confidential_radio"),
(SELECT
"type" AS "type_u11",
"value" AS "value_u8",
"__FK_updated_datetime"
FROM "updated_datetime" FK "__FK_updated_datetime"),
(SELECT
"type" AS "type_u14",
"value" AS "value_u11",
"__FK_Status"
FROM "Status" FK "__FK_Status"),
(SELECT
"__KEY_Assignee",
"__FK_Assignee"
FROM "Assignee" PK "__KEY_Assignee" FK "__FK_Assignee"),
(SELECT
"__KEY_attestation_table",
"__FK_attestation_table",
(SELECT
"__KEY_value",
"__FK_value_u3",
(SELECT
"__KEY_value_u0",
"__FK_value_u4"
FROM "value" PK "__KEY_value_u0" FK "__FK_value_u4")
FROM "value" PK "__KEY_value" FK "__FK_value_u3")
FROM "attestation_table" PK "__KEY_attestation_table" FK "__FK_attestation_table"),
(SELECT
"type" AS "type_u24",
"value" AS "value_u19",
"__FK_payment_radio"
FROM "payment_radio" FK "__FK_payment_radio"),
(SELECT
"type" AS "type_u25",
"value" AS "value_u20",
"__FK_personal_radio"
FROM "personal_radio" FK "__FK_personal_radio"),
(SELECT
"__KEY_created_by",
"__FK_created_by"
FROM "created_by" PK "__KEY_created_by" FK "__FK_created_by"),
(SELECT
"type" AS "type_u28",
"__KEY_requestor",
"__FK_requestor",
(SELECT
"code" AS "code_u1",
"name" AS "name_u1",
"__FK_value_u6"
FROM "value" FK "__FK_value_u6")
FROM "requestor" PK "__KEY_requestor" FK "__FK_requestor"),
(SELECT
"type" AS "type_u31",
"value" AS "value_u24",
"__FK_risk_level"
FROM "risk_level" FK "__FK_risk_level"),
(SELECT
"__KEY_updated_by",
"__FK_updated_by"
FROM "updated_by" PK "__KEY_updated_by" FK "__FK_updated_by"),
(SELECT
"type" AS "type_u37",
"value" AS "value_u29",
"__FK_$id"
FROM "$id" FK "__FK_$id")
FROM JSON (wrap off) "records" PK "__KEY_records";

[record_number]:
LOAD [type_u0] AS [type_u0],
[value_u0] AS [value_u0],
[__FK_record_number] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_record_number]);

[access_permissions]:
LOAD [__KEY_access_permissions]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_access_permissions]);

[number_of_records]:
LOAD [type_u4],
[value_u3],
[__FK_number_of_records] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_number_of_records]);

[Attachment]:
LOAD [__KEY_Attachment]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Attachment]);

[project_name]:
LOAD [type_u6] AS [type_u6],
[value_u4] AS SPBDProjectName,
[type_u8] AS [type_u8],
[value_u5] AS SPBDConfidential,
[__FK_project_name] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_project_name]);
STORE [project_name] INTO lib://QVD_S1/InfoSec/OBJ_SPBD_Operational.QVD;

[project_types]:
LOAD [__KEY_project_types]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_project_types]);

[confidential_radio]:
LOAD [type_u8] AS [type_u8],
[value_u5] AS SPBDConfidential,
[__FK_confidential_radio] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_confidential_radio]);
//STORE [confidential_radio] INTO lib://QVD_S1/InfoSec/OBJ_SPBD_Operational.QVD;

[updated_datetime]:
LOAD [type_u11] AS [type_u11],
[value_u8] AS [value_u8],
[__FK_updated_datetime] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_updated_datetime]);

[Status]:
LOAD [type_u14],
[value_u11],
[__FK_Status] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Status]);

[Assignee]:
LOAD [__KEY_Assignee]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Assignee]);

[value_u3]:
LOAD [__KEY_value_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value_u4]);

[value_u4]:
LOAD [__KEY_value]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value_u3]);

[attestation_table]:
LOAD [__KEY_attestation_table]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_attestation_table]);

[payment_radio]:
LOAD [type_u24],
[value_u19],
[__FK_payment_radio] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_payment_radio]);

[personal_radio]:
LOAD [type_u25] AS [type_u25],
[value_u20] AS [value_u20],
[__FK_personal_radio] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_personal_radio]);

[created_by]:
LOAD [__KEY_created_by]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_created_by]);

[value_u6]:
LOAD [code_u1],
[name_u1],
[__FK_value_u6] AS [__KEY_requestor]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value_u6]);

[requestor]:
LOAD [type_u28] AS [type_u28],
[__KEY_requestor],
[__FK_requestor] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_requestor]);

[risk_level]:
LOAD [type_u31] AS [type_u31],
[value_u24] AS [value_u24],
[__FK_risk_level] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_risk_level]);

[updated_by]:
LOAD [__KEY_updated_by]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_updated_by]);

[$id]:
LOAD [type_u37] AS [type_u37],
[value_u29] AS [value_u29],
[__FK_$id] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_$id]);

[records]:
LOAD [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_records]);

DROP TABLE RestConnectorMasterTable;

However, I need to set the actual information from each select statement, combine it into a single table and store it in a QVD file. I've attached the output from the API call, with all of the data that I need present.

I need to get a single table from this with one row per project (the main data type from the API call) and all of the fields included. There are only 3 projects in this file for now.