Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Did you get this working?