topic Re: Fetch Data with the Qlik Jira Connector in Connectivity & Data Prep

Fetch Data with the Qlik Jira Connector

Fq — Sat, 13 Apr 2024 20:34:12 GMT

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=''
);"

Re: Fetch Data with the Qlik Jira Connector

igoralcantara — Sun, 14 Apr 2024 03:23:25 GMT

Is that a custom field? If so, you need to get, from tje customfields table, the name of the custom field and add to your script.

Here is an example with a custom field ID equals to 10010.

There are 2 steps.

1. add this to the SQL in the list of fields.

fields_customfield_10010_XML

2. Add this to the Qlik preceding load:

TextBetween(fields_customfield_10010_XML, '<name>', '</name>') As [Request Type]

I hope this helps.

Re: Fetch Data with the Qlik Jira Connector

Fq — Sun, 14 Apr 2024 19:03:00 GMT

Hi igoralcantara, thanks for your answer.

I found the following information in a table called "Fields" that appears with the integrated Jira Connector.

I can see it says false in the custom column, so not sure it is considered as a custom field, so not sure how I should proceed.

Best regards,

FQ.

Re: Fetch Data with the Qlik Jira Connector

igoralcantara — Mon, 15 Apr 2024 12:12:36 GMT

If that field matches the field you are looking for, then, this is your guy.

Just use my logic as an example but with your field ID instead.

Re: Fetch Data with the Qlik Jira Connector

Fq — Wed, 24 Apr 2024 13:29:43 GMT

Hi Igor,

It didn´t work, but also I noticed that is not the field.

I did a get API call using python to the issues JIRA data, and got a JSON response where the field is within a dictionary called "timetracking". What I need is originalEstimate.

"timetracking":{"originalEstimate":"1w 2d","remainingEstimate":"1w 1d 4h","timeSpent":"4h","originalEstimateSeconds":201600,"remainingEstimateSeconds":187200,"timeSpentSeconds":14400}

However not sure how to achieve that with the SQL list of fields or using your logic.

Best regards,

Francisco.

Re: Fetch Data with the Qlik Jira Connector

domanski — Wed, 05 Jun 2024 12:29:32 GMT

We have the same problem, the field "timeoriginalestimate" is not in the issues table. And you cannot add the field as a custom field because it has no ID as shown in the screenshot.

Re: Fetch Data with the Qlik Jira Connector

greg_lag — Wed, 29 Jan 2025 14:47:29 GMT

Were you able to fix this issue? Did you found the solution how to retrive informaction about timetracking?

Re: Fetch Data with the Qlik Jira Connector

greg_lag — Wed, 29 Jan 2025 14:48:43 GMT

did you fount the solution?

Re: Fetch Data with the Qlik Jira Connector

EugeniaCastilla — Wed, 14 May 2025 12:44:07 GMT

Hey Greg... I have the exact same issue, did you find a solution for it?

This is very annoying

Cheers,

Eugenia Castilla

Re: Fetch Data with the Qlik Jira Connector

RafaelBarrios — Wed, 14 May 2025 21:58:27 GMT

Hi @Fq

i guess your field is in the custom fields table, so you will need to find the field ID by using the connector

once you have the field ID you can add it to your issues query

your script will look like this one

and field will be available

once you make it work, you can try first query "fields" table and dinamicaly add them to your "issues" query.
important: there is a limitation on the number of fields o characters you can add in the customsID fiels, so you may need to iterate to load all fields

hope this helps

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!

Re: Fetch Data with the Qlik Jira Connector

Martizi — Mon, 28 Jul 2025 14:14:37 GMT

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=timeoriginalestimate");

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=timeoriginalestimate");

END IF
NEXT vStart

Re: Fetch Data with the Qlik Jira Connector

Martizi — Tue, 29 Jul 2025 20:07:18 GMT

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=timeoriginalestimate&orderby=id");

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=timeoriginalestimate&orderby=id");

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;

Re: Fetch Data with the Qlik Jira Connector

EmmaDoherty — Wed, 04 Feb 2026 11:02:01 GMT

It is not a custom field though.