Fetch Data with the Qlik Jira Connector

[Fq]

Is anyone familiar with the Qlik Sense Jira integrated connector?

I'm trying to fetch a specific field from Jira: the Issue's original estimated time.

However, the provided Jira connector script for issues doesn't seem to include this importanr field, which strikes me as odd.

Does anyone have insight or suggestions on how I might access this field? I believe its original name is 'originalEstimate'."

Jira Connector script for issues:

"SELECT
id,
key,
fields_summary,
fields_description,
fields_environment,
fields_created,
fields_lastViewed,
fields_updated,
fields_resolutiondate,
fields_duedate,
fields_labels,
fields_components_ids,
fields_components_names,
fields_fixVersions_ids,
fields_fixVersions_names,
fields_versions_ids,
fields_versions_names,
fields_creator_accountId,
fields_creator_active,
fields_creator_displayName,
fields_creator_emailAddress,
fields_creator_key,
fields_creator_name,
fields_creator_timeZone,
fields_assignee_accountId,
fields_assignee_active,
fields_assignee_displayName,
fields_assignee_emailAddress,
fields_assignee_key,
fields_assignee_name,
fields_assignee_timeZone,
fields_reporter_accountId,
fields_reporter_active,
fields_reporter_displayName,
fields_reporter_emailAddress,
fields_reporter_key,
fields_reporter_name,
fields_reporter_timeZone,
fields_issuetype_description,
fields_issuetype_iconUrl,
fields_issuetype_id,
fields_issuetype_name,
fields_issuetype_subtask,
fields_priority_iconUrl,
fields_priority_id,
fields_priority_name,
fields_status_id,
fields_status_name,
fields_status_description,
fields_status_iconUrl,
fields_resolution_id,
fields_resolution_name,
fields_resolution_description,
fields_votes_hasVoted,
fields_votes_votes,
fields_watches_isWatching,
fields_watches_watchCount,
fields_parent_key,
fields_parent_id,
fields_subtasks_ids,
fields_subtasks_keys,
fields_issuelinks_ids,
fields_issuelinks_keys,
fields_outwardIssueLinks,
fields_inwardIssueLinks
FROM Issues
WITH PROPERTIES (
projectIdOrKey='$(vProjectId)',
createdAfter='',
createdBefore='',
updatedAfter='', 
updatedBefore='',
customFieldIds='',
jqlQuery='',
maxResults=''
);"

[Martizi]

Hi!
You need to create a REST connection to Jira, then in the editor retrieve the total number of issues in your cloud :

LIB CONNECT TO 'REST_JIRA';

RestTotal:
SQL SELECT
"total"
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "https://xxxx/rest/api/3/search?jql=&startAt=0&maxResults=0&fields=timeoriginalestimate");

LET vTotal = Peek('total', 0, 'RestTotal');

DROP TABLE RestTotal;

After that, create a loop to load all the timeoriginalestimate values for all the issues :

LET vPageSize = 100;
LET vNumPages = Ceil (vTotal / vPageSize);

FOR vStart = 0 TO (vNumPages)-1 STEP 1
LET vOffset = (vStart) * (vPageSize);

IF vStart = 0 THEN

IssuesPaged:
SQL SELECT
"expand" AS "expand_u0",
"startAt",
"maxResults",
"total",
"__KEY_root",
(SELECT
"expand",
"id",
"self",
"key",
"__KEY_issues",
"__FK_issues",
(SELECT
"timeoriginalestimate",
"__FK_fields"
FROM "fields" FK "__FK_fields")
FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "https://xxx/rest/api/3/search?jql=&startAt=$(vOffset)&maxResults=$(vPageSize)&fields=timeoriginalest...");

ELSE

CONCATENATE(IssuesPaged)
SQL SELECT
"expand" AS "expand_u0",
"startAt",
"maxResults",
"total",
"__KEY_root",
(SELECT
"expand",
"id",
"self",
"key",
"__KEY_issues",
"__FK_issues",
(SELECT
"timeoriginalestimate",
"__FK_fields"
FROM "fields" FK "__FK_fields")
FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "https://xxx/rest/api/3searchjql=&startAt=$(vOffset)&maxResults=$(vPageSize)&fields=timeoriginalestim...");

END IF
NEXT vStart

[Martizi]

If you want to have the Key and TimeOriginalEstimate fields linked for all tickets:

LIB CONNECT TO 'REST_JIRA';

RestTotal:
SQL SELECT
"total"
FROM JSON (wrap on) "root"
WITH CONNECTION (
URL "https://xxx/rest/api/3/search?jql=&startAt=0&maxResults=0&fields=timeoriginalestimate");

LET vTotal = Peek('total', 0, 'RestTotal');

DROP TABLE RestTotal;

LET vPageSize = 100;
LET vNumPages = Ceil (vTotal / vPageSize);

FOR vStart = 0 TO (vNumPages)-1 STEP 1
LET vOffset = (vStart) * (vPageSize);

IF vStart = 0 THEN

IssuesPaged:
SQL SELECT
"__KEY_root",
(SELECT
"expand",
"id",
"self",
"key",
"__KEY_issues",
"__FK_issues",
(SELECT
"timeoriginalestimate",
"__FK_fields"
FROM "fields" FK "__FK_fields")
FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "https://xxx/rest/api/3/search?jql=&startAt=$(vOffset)&maxResults=$(vPageSize)&fields=timeoriginalest...");

Key:
Load
key,
__KEY_issues

RESIDENT IssuesPaged
where not isnull(key);

Time:
Load
timeoriginalestimate/3600 AS StimaOriginale ,
__FK_fields as __KEY_issues

RESIDENT IssuesPaged;

ELSE

TempPaged:
NoConcatenate
SQL SELECT
"__KEY_root",
(SELECT
"expand",
"id",
"self",
"key",
"__KEY_issues",
"__FK_issues",
(SELECT
"timeoriginalestimate",
"__FK_fields"
FROM "fields" FK "__FK_fields")
FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "https://xxx/rest/api/3/search?jql=&startAt=$(vOffset)&maxResults=$(vPageSize)&fields=timeoriginalest...");

CONCATENATE(Key)
LOAD
key,
__KEY_issues &'-'& $(vStart) as __KEY_issues
RESIDENT TempPaged
where not isnull(key);

CONCATENATE(Time)
LOAD
timeoriginalestimate/3600 as StimaOriginale,
__FK_fields &'-'& $(vStart) as __KEY_issues
RESIDENT TempPaged;

CONCATENATE(IssuesPaged)
LOAD *
RESIDENT TempPaged;

DROP TABLE TempPaged;

END IF
NEXT vStart

DROP TABLE IssuesPaged;

[EmmaDoherty]

It is not a custom field though.