Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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.

Highlighted
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
Highlighted
Creator
Creator

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

Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

Felipe,

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

Take care and stay safe,

Ozzie