Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

pinchofsalt
New Contributor III

How to restructure REST API data

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;

1 Solution

Accepted Solutions
pinchofsalt
New Contributor III

Re: How to restructure REST API data

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

1 Reply
pinchofsalt
New Contributor III

Re: How to restructure REST API data

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