5 Replies Latest reply: Mar 5, 2018 8:55 AM by omar bensalem RSS

    Date Restriction

    Mahitha M

      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";

        • Re: Date Restriction
          omar bensalem

          If we're taliking about with connection suntax,

          It would be sthing like this; please try to adapt it to ur script:

           

          Capture.PNG

          Capture.PNG

           

          and ps: I think that a simple where condition should do the work:

           

          Load *

          from source where date>'01/01/2017';

            • Re: Date Restriction
              Mahitha M

              Hi Omar,

               

              Thanks for your reply. In the attached screen shot is BODY and URL are the fields?

              In my case i have the Create Date in Select statement same like Creation_Date in your script.

               

              Can i apply like this.Its JIRA Script which is like JSON Format. Is normal qlik date variable will work here please let me know.

               

              Let vDate=Date(addmonths(Today(),-12),'YYMMDD');

              Sql

              ---

              Select

              ---

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

              FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION(

              "created"  "$(vDate)"

              )

               

              Thanks in advance.

                • Re: Date Restriction
                  omar bensalem

                  body and url are not fields; but key words to use with "with connection" statement.

                  In fact with connection body : body replaces the request body u send in the rest connector:

                  Capture.PNG

                   

                  while with connection URL replaces the url entered in ur rest connector (to make it dynamic..)

                  Capture.PNG

                    • Re: Date Restriction
                      Mahitha M

                      Hi Omar,

                       

                      Thanks for your help.

                      Some points were clear.

                      My Rest connector URL Is :https://xxxxxxxxxxxxxxx/rest/api/2/search

                      I have extracted the above script by GET Method. For Limiting the data I have to change GET to Post method.

                      In the request body need to enter like below. I have one question here how to enter >01/01/2017 here


                      [

                      {

                      "Created" : "01/01/2017" (how to enter >01/01/2017 here)

                      }

                      ]

                       

                      Thanks in advance.