Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Discussion board where members can learn more about Qlik Sense Data Connectivity.
Hello,
I'm trying to load data from an API into Qlik Sense. The API returns data in a nested JSON format, similar to the following:
{
"0": [
{
first_field: <value>,
second_field: <value>,
subfields: {
first_subfield: <value>,
second_subfield: <value>
}
},
...
]
}
The generated code from the REST connection is similar to the following:
LIB CONNECT TO '<MY_REST_CONNECTION_LIB>';
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"first_field",
"second_field",
"__KEY_0",
"__FK_0",
(SELECT
"first_subfield",
"second_subfield",
"__FK_subfields"
FROM "subfields" FK "__FK_subfields")
FROM "0" PK "__KEY_0" FK "__FK_0")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(URL "<API>");
However, that data is not loaded correctly. The <first_subfield> can have different values for the same <first_field> value. That is, there are the same <first_field> and <second_field> values that occur with different <first_subfield> values. However, in Qlik Sense, for each unique value of <first_field> there is only the first value of <first_subfield> loaded. Some of the rows are missing.
Does someone know where the problem could be?
Thank you in advance!