Skip to main content

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
On-Demand Webinar: Learn proven best practices for a smooth transition to Qlik Sense® SaaS. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cclayford
Partner - Creator
Partner - Creator

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
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

13 Replies
petter
Partner - Champion III
Partner - Champion III

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.

petter
Partner - Champion III
Partner - Champion III

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

petter
Partner - Champion III
Partner - Champion III

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
Partner - Creator
Partner - Creator
Author

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

petter
Partner - Champion III
Partner - Champion III

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
Partner - Creator
Partner - Creator
Author

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

Much appreciated!

ValeriyaBartosh
Partner - Contributor III
Partner - Contributor III

Could you please share your end solution?

arzuatasever
Contributor
Contributor

hi Valeriya,

could you find any solution?

thank you,

ValeriyaBartosh
Partner - Contributor III
Partner - Contributor III

Hello! I used the same way as mentioned above - filtering data via jql - it is the only way which worked for me

ValeriyaBartosh_0-1630995270082.png

 

CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://jira.XXXXXXXXX.ru/rest/api/2/search?jql%2project%2%3CXXXXXXXX%3&fields%2*all;timeout=30;method=GET;
sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=basic;skipServerCertificateValidation=false;
useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;
PaginationType=Offset;OffsetStartField=startAt;IsOffsetStartFieldHeader=0;OffsetStartFieldValue=0;
OffsetCountFieldName=maxResults;IsOffsetCountFieldHeader=0;OffsetCountValue=1000;OffsetTotalPath=total;IsOffsetTotalPathHeader=0;OffsetDataPath=root/total;
allowResponseHeaders=1;allowHttpsOnly=false;XUserId=WBRXWQZFCLYEHadNTDaOO;XPassword=VQWDXSUEQLYGXZdOPTcIWSIGTabKXcEMTJMATSEGPB;";