Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
There are two connection APIs in QlikSense, call them URL1 (issues data) and URL2 (worklogs). There is a limit on getting workflogs records from URL1, only 20 records, pagination is not distributed, so you have to load worklogs from URL2.
How to implement the following:
When loading data from URL1, the script would analyze the value of the "total" field from the [worklog] section, if it is greater than 20, then the key from the field [__KEY_fields] would take the corresponding value of the [self_u4] field from the [worklogs] section and pass it to as URL2, loaded the data. Then the control would go back to the next record from URL1 after "total"> 20, continue to load and analyze the data.
RestConnectorMasterTable:
SQL SELECT
"expand" AS "expand_u0",
"startAt" AS "startAt_u0",
"maxResults" AS "maxResults_u0",
"total" AS "total_u0",
"__KEY_root",
(SELECT
"expand",
"id" AS "id_u5",
………………….
(SELECT
"startAt",
"maxResults",
"total",
"__KEY_worklog",
"__FK_worklog",
(SELECT
"self" AS "self_u4",
"comment",
……………………
FROM "worklogs" PK "__KEY_worklogs" FK "__FK_worklogs")
FROM "worklog" PK "__KEY_worklog" FK "__FK_worklog"),
……………….
[issuetype]:
LOAD [self] AS [self],
[id] AS [id],
[description] AS [description],
[iconUrl] AS [iconUrl],
……………………………………
[worklogs]:
LOAD [self_u4] AS [self_u4],
[comment] AS [comment],
…………………
[issueId] AS [issueId],
[__KEY_worklogs] AS [__KEY_worklogs],
[__FK_worklogs] AS [__KEY_worklog]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_worklogs]);
[worklog]:
LOAD [startAt] AS [startAt],
[maxResults] AS [maxResults],
[total] AS [total],
[__KEY_worklog] AS [__KEY_worklog],
[__FK_worklog] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_worklog]);