Help - REST connection - transform multi-table data into a single table

[Anonymous]

Hi,

For some time now I've been trying to get the multi-table data returned by my SharePoint list REST connection into a single table just like it appears in SharePoint 2013.

Any help here will be very much appreciated.

What I did:

I selected certain fields I require when setting up the connection to the SharePoint list via the Qlik Rest Connector.  Only XML response type seems to work.  After drilling down to properties and selecting 6 of the many fields available, I press insert and it builds the following script.  The fields I require in a single table are at the end of the select statement.

LIB CONNECT TO 'ORIP Recommendations - REST';

RestConnectorMasterTable:

SQL SELECT

"__KEY_feed",

(SELECT

(SELECT

FROM "category"),

(SELECT

FROM "link"),

(SELECT

FROM "author"),

(SELECT

(SELECT

(SELECT

FROM "FileSystemObjectType"),

(SELECT

"attr:type" AS "type_u1",

"@Content" AS "@Content_u0",

"__FK_Id"

FROM "Id" FK "__FK_Id" ContentFieldAlias "@Content_u0"),

(SELECT

FROM "Title"),

(SELECT

"attr:type" AS "type_u2",

"@Content" AS "@Content_u1",

"__FK_RecID"

FROM "RecID" FK "__FK_RecID" ContentFieldAlias "@Content_u1"),

(SELECT

FROM "Existing_x0020_Project"),

(SELECT

FROM "Single_x0020_Action"),

(SELECT

"attr:type" AS "type_u5",

"@Content" AS "@Content_u4",

"__FK_Start_x0020_Date"

FROM "Start_x0020_Date" FK "__FK_Start_x0020_Date" ContentFieldAlias "@Content_u4"),

(SELECT

"attr:type" AS "type_u6",

"attr:null" AS "null_u0",

"@Content" AS "@Content_u5",

"__FK_Finish_x0020_Date"

FROM "Finish_x0020_Date" FK "__FK_Finish_x0020_Date" ContentFieldAlias "@Content_u5"),

(SELECT

FROM "Action_x0020_Plan_x0020_ID"),

(SELECT

FROM "ColorClass"),

(SELECT

FROM "ID"),

(SELECT

FROM "Modified"),

(SELECT

FROM "Created"),

(SELECT

FROM "AuthorId"),

(SELECT

FROM "EditorId"),

(SELECT

FROM "Attachments"),

(SELECT

FROM "GUID"),

(SELECT

FROM "Project_x0020_Name"),

(SELECT

FROM "Management_x0020_Response"),

(SELECT

FROM "Org_x0020_Trans_x0020_ID"),

(SELECT

FROM "Stream"),

(SELECT

FROM "Status_x0020_"),

(SELECT

FROM "Comments"),

(SELECT

FROM "Responsible_x0020_Manager_x0020_"),

(SELECT

"attr:space" AS "space_u5",

"@Content" AS "@Content_u22",

"__FK_Recommendation"

FROM "Recommendation" FK "__FK_Recommendation" ContentFieldAlias "@Content_u22"),

(SELECT

"attr:space" AS "space_u6",

"@Content" AS "@Content_u23",

"__FK_Short_x0020_Title"

FROM "Short_x0020_Title" FK "__FK_Short_x0020_Title" ContentFieldAlias "@Content_u23")

FROM "properties" PK "__KEY_properties" FK "__FK_properties")

FROM "content" PK "__KEY_content" FK "__FK_content")

FROM "entry" PK "__KEY_entry" FK "__FK_entry")

FROM XML "feed" PK "__KEY_feed";

[Id]:

LOAD [type_u1] AS [type_u1],

[@Content_u0] AS [@Content_u0],

[__FK_Id] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Id]);

[RecID]:

LOAD [type_u2] AS [type_u2],

[@Content_u1] AS [@Content_u1],

[__FK_RecID] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_RecID]);

[Start_x0020_Date]:

LOAD [type_u5] AS [type_u5],

[@Content_u4] AS [@Content_u4],

[__FK_Start_x0020_Date] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Start_x0020_Date]);

[Finish_x0020_Date]:

LOAD [type_u6] AS [type_u6],

[null_u0] AS [null_u0],

[@Content_u5] AS [@Content_u5],

[__FK_Finish_x0020_Date] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Finish_x0020_Date]);

[Recommendation]:

LOAD [space_u5] AS [space_u5],

[@Content_u22] AS [@Content_u22],

[__FK_Recommendation] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Recommendation]);

[Short_x0020_Title]:

LOAD [space_u6] AS [space_u6],

[@Content_u23] AS [@Content_u23],

[__FK_Short_x0020_Title] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Short_x0020_Title]);

DROP TABLE RestConnectorMasterTable;

[felipedl]

Hi Ozzie,

All of your REST connectors tables result (after the select statement) have the [__KEY_properties] field, just join all the tables into one by that field and you should have a single table.

Felipe.

[Michael_Tarallo]

Hi Ozzie - in addition to what Felip has described, check out this resource and videos and webinar to see if it helps clarify some information you:

• Qlik Sense REST Connector - Pagination and Linking JSON Schemas
• Qlik Sense Tips and Tricks Webinar - Tapping into all your data - REST Connector - YouTube
• Working with the Qlik REST Connector, Pagination and Multiple JSON Schemas

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

[Anonymous]

Hi Felip and Michael,

Thank you very kindly for responding here.  Will check these out.

Felip, is it too much trouble to ask you to produce a quick example script using 2-3 fields ilustrates to me how to bring these into a single table please?  The data representing the title of each load after the SQL script is held in the fields with the name @content_u.

You help here will certainly set me on the path towards better embedding Qlik Sense within my organisation.  We have many SharePoint lists and are soon to migrate to the Office365 cloud version which the REST connectors will continue to work I'm told.

Thank you both,

Ozzie

[felipedl · Accepted solution]

Ozzie,

You could do something like this:

// Previos commands remains, just copying the relevant parts of the last commands.

[Id]:

LOAD [type_u1] AS [type_u1],

[@Content_u0] AS [@Content_u0],

[__FK_Id] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Id]);

[RecID]:

LOAD [type_u2] AS [type_u2],

[@Content_u1] AS [@Content_u1],

[__FK_RecID] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_RecID]);

[Start_x0020_Date]:

LOAD [type_u5] AS [type_u5],

[@Content_u4] AS [@Content_u4],

[__FK_Start_x0020_Date] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Start_x0020_Date]);

[Finish_x0020_Date]:

LOAD [type_u6] AS [type_u6],

[null_u0] AS [null_u0],

[@Content_u5] AS [@Content_u5],

[__FK_Finish_x0020_Date] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Finish_x0020_Date]);

[Recommendation]:

LOAD [space_u5] AS [space_u5],

[@Content_u22] AS [@Content_u22],

[__FK_Recommendation] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Recommendation]);

[Short_x0020_Title]:

LOAD [space_u6] AS [space_u6],

[@Content_u23] AS [@Content_u23],

[__FK_Short_x0020_Title] AS [__KEY_properties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_Short_x0020_Title]);

NoConcatenate

NewTableAccessPoint:

Load

[type_u1],

[@Content_u0] AS [@Content_u0],

[__FK_Id] AS [__KEY_properties]

Resident Id;

Left join (NewTableAccessPoint)

Load

[__KEY_properties], // Key for the join

[type_u2],

[@Content_u1]

Resident [RecID];

Left join (NewTableAccessPoint)

LOAD

[__KEY_properties], // Key for the join

[type_u5],

[@Content_u4]

Resident [Start_x0020_Date];

Left join (NewTableAccessPoint)

LOAD

[__KEY_properties], // Key for the join

[type_u6],

[null_u0],

[@Content_u5]

RESIDENT [Finish_x0020_Date];

Left join (NewTableAccessPoint)

LOAD

[__KEY_properties], // Key for the join

[space_u5],

[@Content_u22]

RESIDENT [Recommendation];

Left join (NewTableAccessPoint)

LOAD

[__KEY_properties], // Key for the join

[space_u6],

[@Content_u23]

Resident [Short_x0020_Title];

drop tables Id,RecID,Start_x0020_Date,Finish_x0020_Date,Recommendation,Short_x0020_Title

DROP TABLE RestConnectorMasterTable;

// End of script

That should give you a single table.

[Anonymous]

Felipe,

You are awesome.  Thank you and Michael very kindly indeed.

Take care and stay safe,

Ozzie