Skip to main content

Connectivity & Data Prep

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
patrickbender
Contributor III
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 🙂

Labels (2)
0 Replies