Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

cclayford
New Contributor III

JIRA Script - Limit Data

Good day all

I managed to extract JIRA data using the Qlik Rest Connector in Qlik Sense.

I now need to limit the amount of data being pulled with a "where". Ideally i only want to pull data from 2017 onward, based on the "created" field (date field).

Below is the script i am using. It looks like SQL, or JSON SQL, not sure?

Could someone please help me with the line of code i (where clause) need to use to limit the data?

RestConnectorMasterTable:

SQL SELECT

"total" AS "total_u3",

"__KEY_root",

(SELECT

"expand",

"id" AS "id_u29",

// "self" AS "self_u37",

"key" AS "key_u13",

"__KEY_issues",

"__FK_issues",

(SELECT

"customfield_13100",

"resolution",

"customfield_10510",

"customfield_10500",

"customfield_12008",

"customfield_12007",

"customfield_10506",

"customfield_12806",

"customfield_10507",

"customfield_12807",

"lastViewed",

"customfield_14300",

"customfield_12000",

"customfield_12002",

"customfield_12001",

"customfield_12004",

"customfield_12003",

"customfield_12006",

"customfield_12005",

"customfield_11700",

"aggregatetimeoriginalestimate",

"customfield_12107",

"customfield_12106",

"customfield_10601",

"customfield_12901",

"customfield_12108",

"customfield_14400",

"customfield_12101",

"customfield_12100",

"customfield_12102",

"customfield_12105",

"customfield_14401",

"customfield_12217",

"customfield_12216",

"customfield_11008",

"customfield_13300",

"customfield_12211",

"customfield_11002",

"customfield_12213",

"customfield_12212",

"customfield_11003",

"customfield_12215",

"customfield_12214",

"customfield_12206",

"customfield_12205",

"customfield_12208",

"customfield_10700",

"customfield_12207",

"customfield_10701",

"customfield_12209",

"resolutiondate",

"customfield_10020",

"customfield_12200",

"customfield_10021",

"customfield_12202",

"customfield_12201",

"customfield_12204",

"customfield_14500",

"customfield_12203",

"customfield_14501",

"customfield_10016",

"customfield_10017",

"customfield_10810",

"customfield_10018",

"customfield_10019",

"updated" AS "updated_u1",

"timeoriginalestimate",

"description" AS "description_u9",

"customfield_11100",

"customfield_10011",

"customfield_11101",

"customfield_10012",

"customfield_13400",

"customfield_10014",

"customfield_10015",

"customfield_10007",

"customfield_10801",

"customfield_10803",

"customfield_10806",

"customfield_10807",

"customfield_10808",

"customfield_10809",

"summary" AS "summary_u1",

"customfield_10000",

"customfield_10001",

"customfield_12300",

"customfield_10004",

"customfield_10115",

"duedate",

"customfield_10110",

"customfield_10111",

"customfield_11200",

"customfield_13500",

"customfield_11201",

"customfield_10114",

"customfield_14825",

"customfield_14823",

"customfield_14824",

"customfield_10900",

"customfield_10109",

"customfield_12400",

"customfield_14821",

"customfield_14700",

"customfield_14822",

"customfield_14820",

"customfield_12401",

"customfield_14814",

"customfield_14815",

"customfield_14812",

"customfield_14813",

"timeestimate",

"customfield_14818",

"customfield_14819",

"customfield_14816",

"customfield_14817",

"customfield_14810",

"customfield_14811",

"customfield_11301",

"customfield_11302",

"customfield_13600",

"customfield_14804",

"customfield_14801",

"customfield_14009",

"customfield_14802",

"aggregatetimeestimate",

"customfield_14807",

"customfield_14808",

"customfield_14809",

"customfield_14000",

"customfield_14003",

"customfield_14001",

"customfield_14002",

"customfield_14008",

"customfield_14005",

"customfield_14006",

"customfield_13710",

"customfield_10202",

"customfield_12500",

"customfield_13702",

"customfield_13701",

"customfield_13704",

"customfield_13703",

"customfield_13706",

"customfield_13705",

"customfield_13708",

"customfield_13707",

"customfield_13709",

"timespent",

"aggregatetimespent",

"customfield_13700",

"customfield_12602",

"customfield_12601",

"workratio",

"created" AS "created_u2",

"customfield_14100",

"customfield_12600",

"customfield_13801",

"customfield_13800",

"customfield_13001",

"customfield_13000",

"customfield_13002",

"customfield_11500",

"customfield_12700",

"security",

"customfield_14201",

"customfield_14200",

"customfield_10511",

"customfield_13900",

"customfield_10513",

"customfield_10514",

"customfield_10517",

"customfield_10100",

"assignee",

"customfield_10022",

"__KEY_fields",

"__FK_fields",

(SELECT

"id" AS "id_u10",

// "self" AS "self_u10",

"__KEY_issuelinks",

"__FK_issuelinks",

(SELECT

"id",

"name",

"inward",

"outward",

// "self",

"__FK_type"

FROM "type" FK "__FK_type"),

(SELECT

"id" AS "id_u4",

"key" AS "key_u0",

// "self" AS "self_u4",

"__KEY_inwardIssue",

"__FK_inwardIssue",

(SELECT

"summary",

"__KEY_fields_u0",

"__FK_fields_u0",

(SELECT

// "self" AS "self_u1",

"description",

"iconUrl",

"name" AS "name_u1",

"id" AS "id_u1",

"__KEY_status",

"__FK_status",

(SELECT

// "self" AS "self_u0",

"id" AS "id_u0",

"key",

"colorName",

"name" AS "name_u0",

"__FK_statusCategory"

FROM "statusCategory" FK "__FK_statusCategory")

FROM "status" PK "__KEY_status" FK "__FK_status"),

(SELECT

// "self" AS "self_u2",

"iconUrl" AS "iconUrl_u0",

"name" AS "name_u2",

"id" AS "id_u2",

"__FK_priority"

FROM "priority" FK "__FK_priority"),

(SELECT

// "self" AS "self_u3",

"id" AS "id_u3",

"description" AS "description_u0",

"iconUrl" AS "iconUrl_u1",

"name" AS "name_u3",

"subtask",

"avatarId",

"__FK_issuetype"

FROM "issuetype" FK "__FK_issuetype")

FROM "fields" PK "__KEY_fields_u0" FK "__FK_fields_u0")

FROM "inwardIssue" PK "__KEY_inwardIssue" FK "__FK_inwardIssue"),

(SELECT

"id" AS "id_u9",

"key" AS "key_u2",

// "self" AS "self_u9",

"__KEY_outwardIssue",

"__FK_outwardIssue",

(SELECT

"summary" AS "summary_u0",

"__KEY_fields_u1",

"__FK_fields_u1",

(SELECT

// "self" AS "self_u6",

"description" AS "description_u1",

"iconUrl" AS "iconUrl_u2",

"name" AS "name_u5",

"id" AS "id_u6",

"__KEY_status_u0",

"__FK_status_u0",

(SELECT

// "self" AS "self_u5",

"id" AS "id_u5",

"key" AS "key_u1",

"colorName" AS "colorName_u0",

"name" AS "name_u4",

"__FK_statusCategory_u0"

FROM "statusCategory" FK "__FK_statusCategory_u0")

FROM "status" PK "__KEY_status_u0" FK "__FK_status_u0"),

(SELECT

// "self" AS "self_u7",

"iconUrl" AS "iconUrl_u3",

"name" AS "name_u6",

"id" AS "id_u7",

"__FK_priority_u0"

FROM "priority" FK "__FK_priority_u0"),

(SELECT

// "self" AS "self_u8",

"id" AS "id_u8",

"description" AS "description_u2",

"iconUrl" AS "iconUrl_u4",

"name" AS "name_u7",

"subtask" AS "subtask_u0",

"avatarId" AS "avatarId_u0",

"__FK_issuetype_u0"

FROM "issuetype" FK "__FK_issuetype_u0")

FROM "fields" PK "__KEY_fields_u1" FK "__FK_fields_u1")

FROM "outwardIssue" PK "__KEY_outwardIssue" FK "__FK_outwardIssue")

FROM "issuelinks" PK "__KEY_issuelinks" FK "__FK_issuelinks"),

(SELECT

// "self" AS "self_u11",

"name" AS "name_u8",

"key" AS "key_u3",

// "accountId",

"emailAddress",

"displayName",

"active",

"timeZone",

"__KEY_assignee",

"__FK_assignee"

// (SELECT

// "48x48",

// "24x24",

// "16x16",

// "32x32",

// "__FK_avatarUrls"

// FROM "avatarUrls" FK "__FK_avatarUrls")

FROM "assignee" PK "__KEY_assignee" FK "__FK_assignee"),

(SELECT

// "self" AS "self_u12",

"id" AS "id_u11",

"name" AS "name_u9",

"description" AS "description_u3",

"__FK_components"

FROM "components" FK "__FK_components"),

(SELECT

"id" AS "id_u12",

"value",

"__FK_customfield_10600"

FROM "customfield_10600" FK "__FK_customfield_10600"),

(SELECT

"@Value",

"__FK_subtasks"

FROM "subtasks" FK "__FK_subtasks" ArrayValueAlias "@Value"),

(SELECT

// "self" AS "self_u13",

"name" AS "name_u10",

"key" AS "key_u4",

// "accountId" AS "accountId_u0",

"emailAddress" AS "emailAddress_u0",

"displayName" AS "displayName_u0",

"active" AS "active_u0",

// "timeZone" AS "timeZone_u0",

"__KEY_reporter",

"__FK_reporter"

// (SELECT

// "48x48" AS "48x48_u0",

// "24x24" AS "24x24_u0",

// "16x16" AS "16x16_u0",

// "32x32" AS "32x32_u0",

// "__FK_avatarUrls_u0"

// FROM "avatarUrls" FK "__FK_avatarUrls_u0")

FROM "reporter" PK "__KEY_reporter" FK "__FK_reporter"),

(SELECT

"progress",

"total",

"percent",

"__FK_progress"

FROM "progress" FK "__FK_progress"),

(SELECT

// "self" AS "self_u14",

"votes",

"hasVoted",

"__FK_votes"

FROM "votes" FK "__FK_votes"),

(SELECT

"startAt",

"maxResults",

"total" AS "total_u0",

"__KEY_worklog",

"__FK_worklog",

(SELECT

// "self" AS "self_u17",

"comment",

"created",

"updated",

"started",

"timeSpent",

"timeSpentSeconds",

"id" AS "id_u13",

"issueId",

"__KEY_worklogs",

"__FK_worklogs",

(SELECT

// "self" AS "self_u15",

"name" AS "name_u11",

"key" AS "key_u5",

// "accountId" AS "accountId_u1",

"emailAddress" AS "emailAddress_u1",

"displayName" AS "displayName_u1",

"active" AS "active_u1",

// "timeZone" AS "timeZone_u1",

"__KEY_author",

"__FK_author"

// (SELECT

// "48x48" AS "48x48_u1",

// "24x24" AS "24x24_u1",

// "16x16" AS "16x16_u1",

// "32x32" AS "32x32_u1",

// "__FK_avatarUrls_u1"

// FROM "avatarUrls" FK "__FK_avatarUrls_u1")

FROM "author" PK "__KEY_author" FK "__FK_author"),

(SELECT

// "self" AS "self_u16",

"name" AS "name_u12",

"key" AS "key_u6",

// "accountId" AS "accountId_u2",

"emailAddress" AS "emailAddress_u2",

"displayName" AS "displayName_u2",

"active" AS "active_u2",

// "timeZone" AS "timeZone_u2",

"__KEY_updateAuthor",

"__FK_updateAuthor"

// (SELECT

// "48x48" AS "48x48_u2",

// "24x24" AS "24x24_u2",

// "16x16" AS "16x16_u2",

// "32x32" AS "32x32_u2",

// "__FK_avatarUrls_u2"

// FROM "avatarUrls" FK "__FK_avatarUrls_u2")

FROM "updateAuthor" PK "__KEY_updateAuthor" FK "__FK_updateAuthor")

FROM "worklogs" PK "__KEY_worklogs" FK "__FK_worklogs")

FROM "worklog" PK "__KEY_worklog" FK "__FK_worklog"),

(SELECT

// "self" AS "self_u18",

"id" AS "id_u14",

"description" AS "description_u4",

"iconUrl" AS "iconUrl_u5",

"name" AS "name_u13",

"subtask" AS "subtask_u1",

"avatarId" AS "avatarId_u1",

"__FK_issuetype_u1"

FROM "issuetype" FK "__FK_issuetype_u1"),

(SELECT

// "self" AS "self_u20",

"id" AS "id_u16",

"key" AS "key_u7",

"name" AS "name_u15",

"projectTypeKey",

"__KEY_project",

"__FK_project",

// (SELECT

// "48x48" AS "48x48_u3",

// "24x24" AS "24x24_u3",

// "16x16" AS "16x16_u3",

// "32x32" AS "32x32_u3",

// "__FK_avatarUrls_u3"

// FROM "avatarUrls" FK "__FK_avatarUrls_u3"),

(SELECT

// "self" AS "self_u19",

"id" AS "id_u15",

"description" AS "description_u5",

"name" AS "name_u14",

"__FK_projectCategory"

FROM "projectCategory" FK "__FK_projectCategory")

FROM "project" PK "__KEY_project" FK "__FK_project"),

(SELECT

"@Value" AS "@Value_u0",

"__FK_customfield_10702"

FROM "customfield_10702" FK "__FK_customfield_10702" ArrayValueAlias "@Value_u0"),

(SELECT

// "self" AS "self_u21",

"watchCount",

"isWatching",

"__FK_watches"

FROM "watches" FK "__FK_watches"),

(SELECT

"id" AS "id_u17",

"value" AS "value_u0",

"__FK_customfield_10022"

FROM "customfield_10022" FK "__FK_customfield_10022"),

(SELECT

"originalEstimate",

"remainingEstimate",

"originalEstimateSeconds",

"remainingEstimateSeconds",

"timeSpent" AS "timeSpent_u0",

"timeSpentSeconds" AS "timeSpentSeconds_u0",

"__FK_timetracking"

FROM "timetracking" FK "__FK_timetracking"),

(SELECT

"@Value" AS "@Value_u1",

"__FK_customfield_10006"

FROM "customfield_10006" FK "__FK_customfield_10006" ArrayValueAlias "@Value_u1"),

(SELECT

"maxResults" AS "maxResults_u0",

"total" AS "total_u1",

"startAt" AS "startAt_u0",

"__KEY_comment",

"__FK_comment",

(SELECT

// "self" AS "self_u24",

"id" AS "id_u18",

"body",

"created" AS "created_u0",

"updated" AS "updated_u0",

"__KEY_comments",

"__FK_comments",

(SELECT

// "self" AS "self_u22",

"name" AS "name_u16",

"key" AS "key_u8",

// "accountId" AS "accountId_u3",

"emailAddress" AS "emailAddress_u3",

"displayName" AS "displayName_u3",

"active" AS "active_u3",

// "timeZone" AS "timeZone_u3",

"__KEY_author_u0",

"__FK_author_u0"

// (SELECT

// "48x48" AS "48x48_u4",

// "24x24" AS "24x24_u4",

// "16x16" AS "16x16_u4",

// "32x32" AS "32x32_u4",

// "__FK_avatarUrls_u4"

// FROM "avatarUrls" FK "__FK_avatarUrls_u4")

FROM "author" PK "__KEY_author_u0" FK "__FK_author_u0"),

(SELECT

// "self" AS "self_u23",

"name" AS "name_u17",

"key" AS "key_u9",

// "accountId" AS "accountId_u4",

"emailAddress" AS "emailAddress_u4",

"displayName" AS "displayName_u4",

"active" AS "active_u4",

// "timeZone" AS "timeZone_u4",

"__KEY_updateAuthor_u0",

"__FK_updateAuthor_u0"

// (SELECT

// "48x48" AS "48x48_u5",

// "24x24" AS "24x24_u5",

// "16x16" AS "16x16_u5",

// "32x32" AS "32x32_u5",

// "__FK_avatarUrls_u5"

// FROM "avatarUrls" FK "__FK_avatarUrls_u5")

FROM "updateAuthor" PK "__KEY_updateAuthor_u0" FK "__FK_updateAuthor_u0")

FROM "comments" PK "__KEY_comments" FK "__FK_comments")

FROM "comment" PK "__KEY_comment" FK "__FK_comment"),

(SELECT

// "self" AS "self_u25",

"id" AS "id_u19",

"description" AS "description_u6",

"name" AS "name_u18",

"archived",

"released",

"releaseDate",

"__FK_fixVersions"

FROM "fixVersions" FK "__FK_fixVersions"),

(SELECT

// "self" AS "self_u26",

"value" AS "value_u1",

"id" AS "id_u20",

"__FK_customfield_10100"

FROM "customfield_10100" FK "__FK_customfield_10100"),

(SELECT

// "self" AS "self_u27",

"iconUrl" AS "iconUrl_u6",

"name" AS "name_u19",

"id" AS "id_u21",

"__FK_priority_u1"

FROM "priority" FK "__FK_priority_u1"),

(SELECT

// "self" AS "self_u29",

"description" AS "description_u7",

"iconUrl" AS "iconUrl_u7",

"name" AS "name_u21",

"id" AS "id_u23",

"__KEY_status_u1",

"__FK_status_u1",

(SELECT

// "self" AS "self_u28",

"id" AS "id_u22",

"key" AS "key_u10",

"colorName" AS "colorName_u1",

"name" AS "name_u20",

"__FK_statusCategory_u1"

FROM "statusCategory" FK "__FK_statusCategory_u1")

FROM "status" PK "__KEY_status_u1" FK "__FK_status_u1"),

(SELECT

// "self" AS "self_u30",

"name" AS "name_u22",

"key" AS "key_u11",

// "accountId" AS "accountId_u5",

"emailAddress" AS "emailAddress_u5",

"displayName" AS "displayName_u5",

"active" AS "active_u5",

// "timeZone" AS "timeZone_u5",

"__KEY_creator",

"__FK_creator"

// (SELECT

// "48x48" AS "48x48_u6",

// "24x24" AS "24x24_u6",

// "16x16" AS "16x16_u6",

// "32x32" AS "32x32_u6",

// "__FK_avatarUrls_u6"

// FROM "avatarUrls" FK "__FK_avatarUrls_u6")

FROM "creator" PK "__KEY_creator" FK "__FK_creator"),

(SELECT

"progress" AS "progress_u0",

"total" AS "total_u2",

"percent" AS "percent_u0",

"__FK_aggregateprogress"

FROM "aggregateprogress" FK "__FK_aggregateprogress"),

(SELECT

// "self" AS "self_u32",

"id" AS "id_u24",

"filename",

"created" AS "created_u1",

"size",

"mimeType",

"content",

"thumbnail",

"__KEY_attachment",

"__FK_attachment",

(SELECT

// "self" AS "self_u31",

"name" AS "name_u23",

"key" AS "key_u12",

// "accountId" AS "accountId_u6",

"emailAddress" AS "emailAddress_u6",

"displayName" AS "displayName_u6",

"active" AS "active_u6",

// "timeZone" AS "timeZone_u6",

"__KEY_author_u1",

"__FK_author_u1"

// (SELECT

// "48x48" AS "48x48_u7",

// "24x24" AS "24x24_u7",

// "16x16" AS "16x16_u7",

// "32x32" AS "32x32_u7",

// "__FK_avatarUrls_u7"

// FROM "avatarUrls" FK "__FK_avatarUrls_u7")

FROM "author" PK "__KEY_author_u1" FK "__FK_author_u1")

FROM "attachment" PK "__KEY_attachment" FK "__FK_attachment"),

(SELECT

// "self" AS "self_u33",

"value" AS "value_u2",

"id" AS "id_u25",

"__FK_customfield_10500"

FROM "customfield_10500" FK "__FK_customfield_10500"),

(SELECT

// "self" AS "self_u34",

"value" AS "value_u3",

"id" AS "id_u26",

"__FK_customfield_10506"

FROM "customfield_10506" FK "__FK_customfield_10506"),

(SELECT

// "self" AS "self_u35",

"id" AS "id_u27",

"description" AS "description_u8",

"name" AS "name_u24",

"__FK_resolution"

FROM "resolution" FK "__FK_resolution"),

(SELECT

// "self" AS "self_u36",

"value" AS "value_u4",

"id" AS "id_u28",

"__FK_customfield_10109"

FROM "customfield_10109" FK "__FK_customfield_10109")

FROM "fields" PK "__KEY_fields" FK "__FK_fields")

FROM "issues" PK "__KEY_issues" FK "__FK_issues")

FROM JSON (wrap on) "root" PK "__KEY_root";

Many thanks,

Christopher.

1 Solution

Accepted Solutions
MVP
MVP

Re: JIRA Script - Limit Data

The JQL that can be embedded in the right kind of Jira REST API calls can be learned here:

6 Replies
MVP
MVP

Re: JIRA Script - Limit Data

You should strive to limit the data as early as possible in your data logistics chain. That is especially true for REST API based sources. Since REST is rather in-efficient and slow compared to database/table oriented sources.

So try to get the REST API call to include the limits you need. Jira has what is called JQL, search and filters that could be used for limiting the data: JIRA 7.6.1

The SELECT in itself is a proprietary kind of rather simplistic but hierarchy aware SELECT which does not support a WHERE clause. Have a look here:

http://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/1.2/Load-REST-data/Sele...

If you can't limit in the REST API call you can do a resident load after the SELECT in your load script and then in the LOAD statement have a WHERE clause. But that is as previously mentioned very inefficient - at least with larger amounts of data.

MVP
MVP

Re: JIRA Script - Limit Data

The JQL that can be embedded in the right kind of Jira REST API calls can be learned here:

MVP
MVP

Re: JIRA Script - Limit Data

Maybe a third-party Jira ODBC connector could be worth the investment instead of handling all the intricacies of REST:

ODBC Driver for Jira | Atlassian Marketplace

Simba: https://www.simba.com/drivers/jira-odbc-jdbc/

CDATA: JIRA ODBC Driver - ODBC | ODBC Drivers | ODBC Connection | ODBC API

cclayford
New Contributor III

Re: JIRA Script - Limit Data

Would i be able to put my search criteria in the URL section of the REST Connector?

Currently i am filtering on Project and it works.

How do i do the same for the "created" field?

https://xxxxxxxxxxxxxxx/rest/api/2/search?fields=*all&jql=project%20in%20("Business%20As%20Usual")

Untitled.png

MVP
MVP

Re: JIRA Script - Limit Data

You just do an AND just as shown in the link I gave you to the documentation.

If the JQL string gets too long you can do a POST request - also mentioned in the documentation.

Instead of adding it to the URL property you could add the query parameters it the WITH CONNECTION clause of the SELECT statement in the load script.

cclayford
New Contributor III

Re: JIRA Script - Limit Data

Thank you so much! The documentation you shared with me pointed me in the right direction.

Much appreciated!

Community Browser