SharePoint Data to Qlik Sense

aamer_telent — Tue, 28 Dec 2021 19:59:42 GMT

I have following script generated by Qlik Rest Connector. I have modified to include pagination.

It works fine for properties table and upto first 100 records but data field ("__KEY_properties") in other tables such as recIssueDate and recRevision, is repeating for each record and it is not correct.

not sure where i am making the mistake.

LIB CONNECT TO 'ECM';

let total=60000;
Let totalfetched = 0;
Let startAt = 0;
Let pageSize = 100;
let vCount=0;

for startAt = 0 to total step pageSize

let vCount=$(vCount)+1;

RestConnectorMasterTable:
SQL SELECT
"attr:base" AS "base",
"attr:xmlns" AS "xmlns",
"attr:d" AS "d",
"attr:m" AS "m",
"attr:georss" AS "georss",
"attr:gml" AS "gml",
"id" AS "id_u0",
"title" AS "title_u0",
"updated" AS "updated_u0",
"__KEY_feed",
(SELECT
"attr:etag" AS "etag",
"id",
"title",
"updated",
"__KEY_entry",
"__FK_entry",
(SELECT
"attr:term" AS "term",
"attr:scheme" AS "scheme",
"__FK_category"
FROM "category" FK "__FK_category"),
(SELECT
"attr:rel" AS "rel",
"attr:href" AS "href",
"__FK_link"
FROM "link" FK "__FK_link"),
(SELECT
"name",
"__FK_author"
FROM "author" FK "__FK_author"),
(SELECT
"attr:type" AS "type_u3",
"__KEY_content",
"__FK_content",
(SELECT
"Title",
"recDocumentNumber",
"EncodedAbsUrl",
"__KEY_properties",
"__FK_properties",
(SELECT
"attr:type" AS "type",
"@Content",
"__FK_Id"
FROM "Id" FK "__FK_Id" ContentFieldAlias "@Content"),
(SELECT
"attr:type" AS "type_u0",
"attr:null" AS "null",
"@Content" AS "@Content_u0",
"__FK_recIssueDate"
FROM "recIssueDate" FK "__FK_recIssueDate" ContentFieldAlias "@Content_u0"),
(SELECT
"attr:type" AS "type_u1",
"@Content" AS "@Content_u1",
"__FK_recRevision"
FROM "recRevision" FK "__FK_recRevision" ContentFieldAlias "@Content_u1"),
(SELECT
"attr:type" AS "type_u2",
"@Content" AS "@Content_u2",
"__FK_ID"
FROM "ID" FK "__FK_ID" ContentFieldAlias "@Content_u2"),
(SELECT
"attr:null" AS "null_u0",
"@Content" AS "@Content_u3",
"__FK_recDocumentStatus"
FROM "recDocumentStatus" FK "__FK_recDocumentStatus" ContentFieldAlias "@Content_u3")
FROM "properties" PK "__KEY_properties" FK "__FK_properties")
FROM "content" PK "__KEY_content" FK "__FK_content")
FROM "entry" PK "__KEY_entry" FK "__FK_entry"),
(SELECT
"attr:rel" AS "rel_u0",
"attr:href" AS "href_u0",
"__FK_link_u0"
FROM "link" FK "__FK_link_u0")
FROM XML "feed" PK "__KEY_feed"
// ;
with Connection (Url "$(vURI)");

let vURI=FieldValue('href_u0', $(vCount));
trace Count Value is $(vCount);
exit for when isnull(vURI);
next startAt;

topic SharePoint Data to Qlik Sense in Connectivity & Data Prep

SharePoint Data to Qlik Sense