How to restructure REST API data

[Anonymous]

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

        (SELECT

            (SELECT

                (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;

[Anonymous · Accepted solution]

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

[khalander]

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