0 Replies Latest reply: Sep 14, 2018 8:01 AM by Lanevich Vladimir RSS

    A script for loading data with issues and worklogs from JIRA via Rest API in JSON format

    Lanevich Vladimir

      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]);