How to restructure REST API data

Anonymous — Wed, 04 May 2016 07:28:53 GMT

I'm using the REST API connector in sense but for each row the data has the format:

<Row#><fieldname><value><fieldname><value><fieldname><value><fieldname><value><fieldname><value>

The XML (or JSON) script imports this into three columns, record number, one with the repeated field names and one with the actual values. (See screen shot of a subset of the data attached).

How can I restructure this into a normal table?

Note, by default null fields are excluded so each row may have a different set of fields but I can send a parameter to pull out null values of a record if that helps.

Code inserted for reference below.

RestConnectorMasterTable:

SQL SELECT

"__KEY_response",

(SELECT

"attr:val" AS "val",

"@Content",

"__FK_FL"

FROM "FL" FK "__FK_FL" ContentFieldAlias "@Content")

FROM "row" PK "__KEY_row" FK "__FK_row")

FROM "Opportunities" PK "__KEY_Opportunities" FK "__FK_Opportunities")

FROM "result" PK "__KEY_result" FK "__FK_result")

FROM XML "response" PK "__KEY_response" ;

[FL]:

LOAD [val] AS [val],

[@Content] AS [@Content],

[__FK_FL] AS [__KEY_row]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_FL]);

DROP TABLE RestConnectorMasterTable;

Re: How to restructure REST API data

Anonymous — Tue, 10 May 2016 01:30:51 GMT

I solved this and posted a discussion here: ZOHO API with Qlik REST Connector and table transpose

The key concept I was missing was Generic Load which is explained here: The Generic Load

Re: How to restructure REST API data

khalander — Wed, 09 Sep 2020 14:00:12 GMT

Hi,

I need one help here. I am using similar type of XML response query. I am able to load 2500 records but how to load all available records from data source. Please do the needful.

Thanks Khalander

topic Re: How to restructure REST API data in App Development

How to restructure REST API data

Re: How to restructure REST API data

Re: How to restructure REST API data