Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

Highlighted
patrickbender
New Contributor III

API JSON

Hi,

I'm trying to extract data from an API and get the data in JSON format which leds to a rather nested LOAD script.

is there any way to avoid this:

RestConnectorMasterTable:
SQL SELECT
"name" AS "name_u2",
"owner_user_id",
"project_id",
"__KEY_root",
(SELECT
"name" AS "name_u1",
"description",
"form_id",
"__KEY_form_sections",
"__FK_form_sections",
(SELECT
"name" AS "name_u0",
"__KEY_form_section_records",
"__FK_form_section_records",
(SELECT
"__KEY_form_section_record_values",
"__FK_form_section_record_values",
(SELECT
"__KEY_form_section_record_input_values",
"__FK_form_section_record_input_values",
(SELECT
"last_editor_user_id" AS "last_editor_user_id_u2",
//"project_id" AS "project_id_u2",
"updated_at" AS "updated_at_u2",
"bigint_value" AS "bigint_value_u0",
"string_value" AS "string_value_u0",
"text_value" AS "text_value_u0",
"boolean_value" AS "boolean_value_u0",
"decimal_value" AS "decimal_value_u0",
"datetime_value" AS "datetime_value_u0",
"date_value" AS "date_value_u0",
"__FK_value"
FROM "value" FK "__FK_value")
FROM "form_section_record_input_values" PK "__KEY_form_section_record_input_values" FK "__FK_form_section_record_input_values")
FROM "form_section_record_values" PK "__KEY_form_section_record_values" FK "__FK_form_section_record_values")
FROM "form_section_records" PK "__KEY_form_section_records" FK "__FK_form_section_records")
FROM "form_sections" PK "__KEY_form_sections" FK "__FK_form_sections")
FROM JSON (wrap on) "root" PK "__KEY_root";

[value]:
LOAD [last_editor_user_id_u2] As LastEditedById,
//[project_id_u2] As ,
[updated_at_u2] As FormLastUpdated,

if(not IsNull([string_value_u0]), [string_value_u0],
if(not IsNull([text_value_u0]), [text_value_u0],
if(not IsNull([boolean_value_u0]), [boolean_value_u0],
if(not IsNull([decimal_value_u0]), [decimal_value_u0],
if(not IsNull([datetime_value_u0]), [datetime_value_u0],
if(not IsNull([date_value_u0]), [date_value_u0],
if(not isnull([bigint_value_u0]), [bigint_value_u0]
))))))) As FormDataValue,

[__FK_value] AS [__KEY_form_section_record_input_values]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_value]);


[form_section_record_input_values]:
LOAD [__KEY_form_section_record_input_values],
[__FK_form_section_record_input_values] AS [__KEY_form_section_record_values]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_form_section_record_input_values]);

 

more nested tables follow below I attach more of it if anyone have time to look at it 🙂