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.
