Hi Experts,
Can any one please help me on below requirement.
I have the requirement same like this below thread.
JIRA Script - Limit Data
I have extracted the Search API fields from JIRA using Rest connector in Qliksense using this API URL https://xxxxxxxxxxxxxxx/rest/api/2/search. I got the script like below.
In this script have the created Field which is highlighted in Red color below is a date field. Here I have to pull data from only 2017 on wards. As per the above thread i found that using WITH CONNECTION clause of the SELECT statement in the load script we can restrict the 2017 data.
Please could you add that WITH CONNECTION clause syntax on the below script for my understanding. I am new to REST Connector syntax.
Thanks in advance.
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",
"lastViewed",
"customfield_14300",
"customfield_12000",
"customfield_12002",
"customfield_12001",
"aggregatetimeoriginalestimate",
"customfield_12107",
"customfield_12106",
"customfield_10601",
"resolutiondate",
"customfield_10020",
"customfield_12200",
"updated" AS "updated_u1",
"timeoriginalestimate",
"description" AS "description_u9",
"customfield_11100",
"customfield_10011",
"summary" AS "summary_u1",
"customfield_10000",
"customfield_10115",
"duedate",
"customfield_10110",
"customfield_10111",
"timeestimate",
"customfield_14818",
"customfield_14819",
"aggregatetimeestimate",
"customfield_14807",
"customfield_14808",
"customfield_13709",
"timespent",
"aggregatetimespent",
"customfield_13700",
"customfield_12602",
"customfield_12601",
"workratio",
"created" AS "created_u2",
"customfield_14100",
"customfield_12600",
"security",
"customfield_14201",
"customfield_14200",
"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";
