5 Replies Latest reply: Oct 18, 2017 3:23 PM by Ozzie Boeuf RSS

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

    Ozzie Boeuf

      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;

        • Re: Help - REST connection - transform multi-table data into a single table
          Felip Drechsler

          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.

          • Re: Help - REST connection - transform multi-table data into a single table
            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:

             

             

            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

            • Re: Help - REST connection - transform multi-table data into a single table
              Ozzie Boeuf

              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

                • Re: Help - REST connection - transform multi-table data into a single table
                  Felip Drechsler

                  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.