Qlik Community

Qlik Sense App Development

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

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Loading REST API data

I have a REST API XML Response which has levels as follows:

     Member (memberID)

               Personal (First Name, Last Name)

               Agreement (AgreementNumber, PaymentPlan)

My goal is to Load a single table with one row for each member that looks like:

memberID

First Name

Last Name

AgreementNumber

PaymentPlan

The Data Load Editor produces code that creates 3 separate tables, one for each XML level. The generated code follows. Can anyone suggest how to modify this so that a single table is created instead?

Thanks!

--------------------------------------------------------------------------------------------

RestConnectorMasterTable:

SQL SELECT

  "__KEY_restResponse",

  (SELECT

  FROM "status"),

  (SELECT

  FROM "request"),

  (SELECT

  (SELECT

  "memberId",

  "__FK_member",

  "__KEY_member",

  (SELECT

  "firstName",

  "lastName",

  "__FK_personal"

  FROM "personal" FK "__FK_personal"),

  (SELECT

  "agreementNumber",

  "paymentPlan",

  "__FK_agreement"

  FROM "agreement" FK "__FK_agreement")

  FROM "member" PK "__KEY_member" FK "__FK_member")

  FROM "members" PK "__KEY_members" FK "__FK_members")

FROM XML "restResponse" PK "__KEY_restResponse";

[personal]:

LOAD [firstName] AS [firstName],

  [lastName] AS [lastName]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_personal]);

[agreement]:

LOAD [agreementNumber] AS [agreementNumber],

  [paymentPlan] AS [paymentPlan]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_agreement]);

[member]:

LOAD [memberId] AS [memberId]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_member]);

DROP TABLE RestConnectorMasterTable;