Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

Date Restriction

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

5 Replies
OmarBenSalem
Esteemed Contributor

Re: Date Restriction

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

mahitham
Contributor

Re: Date Restriction

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.

OmarBenSalem
Esteemed Contributor

Re: Date Restriction

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

mahitham
Contributor

Re: Date Restriction

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.

OmarBenSalem
Esteemed Contributor

Re: Date Restriction

I don't think It's possible; it depends on how ur web service is built.

As I said, I think a where condition will do the trick.

sthing like:

Load * from source WHERE DATE >'01/01/2017';

Community Browser