I'm trying to get data from a REST api. The challenge is that the response is nested (JSON format).
I did split up the seperate fields by editing the RSD file, adding something after the xPath...
The result looks better than before but still all my answers are comma seperated in 1 field/row combination instead of 1 field 4 rows for example. It looks like this now:
How can I make sure I only get 1 anwser per value per row? (So line 1, field q_position should be seperated to 4 rows with value 1,2,3 and 4)
Page 1 / 1
Hi Juriaan
Is it possible for you to pull the whole JSON file you get from the call and save it in an document.json or similar?
If so you can try to connect to it with a provider and use the flattendocument option to see if you can get them all in the correct order.
How an rsd file based on that looks should explain what the json path needs to be for it to get only one value.
If not you will need to make an file that is only the Questions part as an individual table.
Then you may need to add an id from the main part to make it have relations to the other area.
I don't really understand what you mean with pulling the whole JSON file and save it into a document.
I'm using the REST 2019 connector. I'm using the Query tool for testing directly on the ODX datasource.
I experimented a bit with the following features in the connector, without result:
Misc>Datamodel: Document or Flattenedfile
Misc>Flatten Arrays: tried a column number
Misc>JSON format: JSON or JSONRows
Connection>XPath: json\pages\questions This returned the following error:
I just removed the fields on root level in the rsd file. The Datamodel is now Flattenedfile.
The data looks like this now. Not sure if it's correct, but via the ID I can relate it to the root fields of the file. So then 1 have 1 table without the nested fields and 1 table with ONLY the nested fields and those I can relate to each other.
In Qlik a load script for this api is automatically generated. There I can see the nested structure is like this:
Hi Juriaan
You can try to use those as the json paths, but the other option you presented in the previous image is the method that will most likely work with the CData provider.
I tried again, started over.
Entered only the base URI and set the DataModel to Relational.
After synchronizing the following RSD files where created:
answers
choices
headings
pages
questions
rows
Some still have some nested fields, but it's more than I had. I think it only needs time to create an acceptable datamodel.
Other remaining challenges:
Looping through all surveyid's. The survey id] field is part of the base URI. I have a table with all available id's, but I guess I have to write a loop in the RSD file so data of all id's are collected.
Pagination, it's a type "next url" with path links/next (100 per page). It's not a default option in the REST connector so I think I need to write some code in the RSD for it.
Good to know, the boolean fields in the source are read as "unknown" datatypes in TX. Resulting in empty tables in the DSA layer. This is solved by the override datatype function in ODX datasource, converting "unknown" to "bit".
Hi Juriaan
The Link/next url can be called like so. <api:set attr="DataModel" value="DOCUMENT" /> <api:set attr="JSONPath" value="$." /> <api:set attr="URI" value="https://webpage.com/api/users" /> <api:set attr="RepeatElement" value="/json/" /> <api:set attr="EnablePaging" value="TRUE" /> <api:set attr="pageurlpath" value="header:Link" />
