02035736917


We can connect to the SharePoint using the inbuilt Rest Connector in Qliksense.

Steps to follow for Rest Connection Setup:
  1. Go to Data Load Editor --> Click Create New Connection --> Select ‘Rest’ under Data sources
URL: Enter SharePoint URL which is like below.
http://yoursite/_api/lists/getbytitle('SharePointList Name')/items?$Top=(enter no of rows for extracting)
Result: http://businessunit.company.com/Recruiting/_api/lists/getbytitle('Contacts')/items?$Top=30000
2. Set Timeout to 30
3. Select GET from the Method drop-down menu.
4. Select Auto detect response type to have the connector detect the format of the SharePoint data set you are accessing.
5. Select Check response type during ‘Test Connection’.
6. Select Sequence ID as the Key generation strategy
7. Use the Windows NTLM setting for Authentication Schema and enter the user name and password for your SharePoint account.
8. Set Use Certificate as No
9. Select Pagination Type as None from the drop down.
10. Skip the Query parameters and Query headers fields because no additional parameters or headers are required.
11. Enter a name for the connection in the Name field and Test Connection.
12. After Connection Successful click on Add Data. Once connect to SharePoint list columns are available in the "properties" table. You have to keep drilling down in the data connection to get to where the properties table appears.


13. The REST Connector supports the following data types:
JSON data
XML data
CSV files


How to Modify Script?
Share point extract each field/Some set of fields as separate load statement. We have to left join all the individual load statements using primary key.

RestConnectorMasterTable:
SQL SELECT
“_KEY_feed”,
(SELECT
(SELECT
FROM “category”),
(SELECT
FROM “link”),
(SELECT
FROM “author”),
(SELECT
(SELECT
“Id”,
“Status”,
“Category”,
“Customers”
(SELECT
“attr:type” AS “type_u3”,
“_FK_FileSystemObjectType"
FROM "FileSystemObjectType" FK "_FK_FileSystemObjectType" ContentFieldAlias "@Content")

[Main Table]:
Load
[null_u1],
[type_u1],
[@Content_u1] as "Category",
[FK_FC_X0020] as "%Key"
Resident RestConnectorMasterTable
where NOT IsNull([FK_FC_X0020]);

left join([Main Table])

Load
[null_u2],
[type_u2],
[@Content_u2] as "link",
[FK_FC_X0021] as "%Key"
Resident RestConnectorMasterTable
where NOT IsNull([FK_FC_X0021]);


left join([Main Table])

Load
[null_u3],
[type_u3],
[@Content_u3] as "author",
[FK_FC_X0022] as "%Key"
Resident RestConnectorMasterTable
where NOT IsNull([FK_FC_X0022]);

left join([Main Table])

Load
“Id”,
“Status”,
“Category”,
“Customers”,
[_KEY_properties],
[_FK_properties] as "%Key"
Resident RestConnectorMasterTable
where NOT IsNull([_FK_properties]);

Drop Table RestConnectorMasterTable;

Store [Main Table] into [$(v.ExtractPath)\Sales\Sales.qvd];
Drop Table [Main Table];