Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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;

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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.

View solution in original post

5 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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
Employee
Employee

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:

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

Regards,
Mike Tarallo
Qlik
Anonymous
Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

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
Not applicable
Author

Felipe,

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

Take care and stay safe,

Ozzie