6 Replies Latest reply: Jan 19, 2018 2:53 AM by Christopher Clayford RSS

    JIRA Script - Limit Data

    Christopher Clayford

      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.