Qlik Sense Rest API Connector for Jira/Getting custom fields with their names from jira to Qlik
Hi Everyone,
Good Evening,
My source is jira, Loaded data from Jira through REST API into qlik sense, while loading Issue table custom fields are loading into Qlik like Custom filed ids as lable, so its difficult to identify what is the actual filed name from jira.
Due to requirement in project few field were added in Jira by Jira developer those fields called custom fields. These newly added fields by Jira developer are coming into qlik as custom filed with id (ex: custom field 250,custom filed 340).
While creating the REST connection defined the filed names in the parameters, even though custom filed with ids are loading.
Do i missed something while loading,please help on this.
Currently we have on premise jira server environment and store jira data in SQL server and then I fetch this data in qliksense to analyse . Now qliksense is going on cloud so my company wants to fetch all data through rest API to Jira server so that when Jira will go to cloud we don't face any issue .
can you please suggest me how can we fetch all the Jira data from Jira server through Rest API (like we are currently doing in Sql database) and use it to qliksense without affecting the existing data model .
I am able to connect the jira server through API but there is limitation we can fetch only 1000 records in one shot also it will not come in desire format .
I tried your example script but its work only 1000 records if I use the same script in loop for next records its not working because of apply map which gives only first record in mapping table but I want all .
I used your sample script to fetch the issues project wise and max records 1000 , its work fine when the records are greater then maxresult but am getting below error when I have less then 1000 records in a project
"The control statement is not correctly matched with its corresponding start statement with do while loop in qliksense"
Could you please suggest how can i remove this error
The iteration number on the loop should be tracked and used in the ApplyMap statements, so each loop uses the correct mapping table (or rows from the mapping table).
There is going to be a rogue end if or next in the code which is throwing things out. I had to hack the code to remove customer sensitive information before uploading it, so that may have caused this.
do while (iPage = 0 or (vMinID <> vLastMin)) and iPage < 300
let vLastMin = vMinID;
// if(vMinID=vLastMin) then // ;
// Set the less than code, or blank it for the first pass let vLessThanID = if(vMinID = 0, '', 'AND id < ' & vMinID);
TempResults:
LOAD * ; SQL SELECT "__KEY_root", (SELECT "expand", "id" AS "id_u6", "self" AS "self_u10", "key" AS "key_u4", "__KEY_issues", "__FK_issues", (SELECT "customfield_10070", "timespent", "customfield_11520", "aggregatetimespent", "customfield_11124", "customfield_11125", "customfield_11720", "customfield_11126", "customfield_11521", "customfield_10820", "resolutiondate", "workratio", "lastViewed", "customfield_10061", "created", "customfield_10141", "customfield_10220", "customfield_12520", "customfield_11821", "customfield_11623", "customfield_11820", "customfield_11622", "customfield_11625", "customfield_11822", "customfield_11624", "timeestimate", "aggregatetimeoriginalestimate", "assignee", "updated", "timeoriginalestimate", "description" AS "description_u3", "customfield_11220", "customfield_11221", "customfield_11421", "customfield_11420", "customfield_11621", "customfield_11620", "customfield_10523", "customfield_10920", "customfield_10921", "customfield_10922", "aggregatetimeestimate", "customfield_10923", "summary", "customfield_11131", "customfield_11132", "customfield_12420", "customfield_10000", "customfield_10320", "customfield_10321", "customfield_11127", "customfield_11920", "customfield_11128", "customfield_11721", "customfield_11129", "customfield_11723", "duedate", "customfield_10190", "resolution", "customfield_10113", "customfield_10114", "customfield_10822", "customfield_10420", "customfield_10160", "__KEY_fields", "__FK_fields", (SELECT "self", "id", "description", "iconUrl", "name", "subtask", "avatarId", "__FK_issuetype" FROM "issuetype" FK "__FK_issuetype"), (SELECT "self" AS "self_u1", "id" AS "id_u1", "key", "name" AS "name_u1", "projectTypeKey", "__KEY_project", "__FK_project", (SELECT "48x48", "24x24", "16x16", "32x32", "__FK_avatarUrls" FROM "avatarUrls" FK "__FK_avatarUrls"), (SELECT "self" AS "self_u0", "id" AS "id_u0", "description" AS "description_u0", "name" AS "name_u0", "__FK_projectCategory" FROM "projectCategory" FK "__FK_projectCategory") FROM "project" PK "__KEY_project" FK "__FK_project"), (SELECT "@Value", "__FK_fixVersions" FROM "fixVersions" FK "__FK_fixVersions" ArrayValueAlias "@Value"), (SELECT "self" AS "self_u2", "id" AS "id_u2", "description" AS "description_u1", "name" AS "name_u2", "__FK_resolution" FROM "resolution" FK "__FK_resolution"), (SELECT "self" AS "self_u3", "watchCount", "isWatching", "__FK_watches" FROM "watches" FK "__FK_watches"), (SELECT "@Value" AS "@Value_u0", "__FK_customfield_10420" FROM "customfield_10420" FK "__FK_customfield_10420" ArrayValueAlias "@Value_u0"), (SELECT "@Value" AS "@Value_u1", "__FK_labels" FROM "labels" FK "__FK_labels" ArrayValueAlias "@Value_u1"), (SELECT "@Value" AS "@Value_u2", "__FK_versions" FROM "versions" FK "__FK_versions" ArrayValueAlias "@Value_u2"), (SELECT "@Value" AS "@Value_u3", "__FK_issuelinks" FROM "issuelinks" FK "__FK_issuelinks" ArrayValueAlias "@Value_u3"), (SELECT "self" AS "self_u5", "description" AS "description_u2", "iconUrl" AS "iconUrl_u0", "name" AS "name_u4", "id" AS "id_u4", "__KEY_status", "__FK_status", (SELECT "self" AS "self_u4", "id" AS "id_u3", "key" AS "key_u0", "colorName", "name" AS "name_u3", "__FK_statusCategory" FROM "statusCategory" FK "__FK_statusCategory") FROM "status" PK "__KEY_status" FK "__FK_status"), (SELECT "@Value" AS "@Value_u4", "__FK_components" FROM "components" FK "__FK_components" ArrayValueAlias "@Value_u4"), (SELECT "self" AS "self_u6", "name" AS "name_u5", "key" AS "key_u1", "emailAddress", "displayName", "active", "timeZone", "__KEY_creator", "__FK_creator", (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 "creator" PK "__KEY_creator" FK "__FK_creator"), (SELECT "@Value" AS "@Value_u5", "__FK_customfield_10081" FROM "customfield_10081" FK "__FK_customfield_10081" ArrayValueAlias "@Value_u5"), (SELECT "@Value" AS "@Value_u6", "__FK_subtasks" FROM "subtasks" FK "__FK_subtasks" ArrayValueAlias "@Value_u6"), (SELECT "self" AS "self_u7", "name" AS "name_u6", "key" AS "key_u2", "emailAddress" AS "emailAddress_u0", "displayName" AS "displayName_u0", "active" AS "active_u0", "timeZone" AS "timeZone_u0", "__KEY_reporter", "__FK_reporter", (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 "reporter" PK "__KEY_reporter" FK "__FK_reporter"), (SELECT "progress", "total", "__FK_aggregateprogress" FROM "aggregateprogress" FK "__FK_aggregateprogress"), (SELECT "progress" AS "progress_u0", "total" AS "total_u0", "__FK_progress" FROM "progress" FK "__FK_progress"), (SELECT "self" AS "self_u8", "name" AS "name_u7", "key" AS "key_u3", "emailAddress" AS "emailAddress_u1", "displayName" AS "displayName_u1", "active" AS "active_u1", "timeZone" AS "timeZone_u1", "__KEY_assignee", "__FK_assignee", (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 "assignee" PK "__KEY_assignee" FK "__FK_assignee"), (SELECT "self" AS "self_u9", "iconUrl" AS "iconUrl_u1", "name" AS "name_u8", "id" AS "id_u5", "__FK_priority" FROM "priority" FK "__FK_priority") FROM "fields" PK "__KEY_fields" FK "__FK_fields") FROM "issues" PK "__KEY_issues" FK "__FK_issues") FROM JSON (wrap on) "root" PK "__KEY_root" QDL WITH CONNECTION ( QUERY "jql" "project=$(vProject) $(vLessThanID) ORDER BY id DESC", QUERY "maxResults" "1000" ) ;
IssueID: Mapping LOAD "__KEY_issues", [id_u6] as IssueID RESIDENT TempResults WHERE (not IsNull("__KEY_issues")) ;
IssueKey: Mapping LOAD "__KEY_issues", [key_u4] as IssueKey RESIDENT TempResults WHERE (not IsNull("__KEY_issues")) ;
Creater: Mapping LOAD "__FK_reporter", capitalize(replace([displayName], '.', ' ')) as Creater RESIDENT TempResults WHERE NOT IsNull([__FK_reporter]) ;
reporter: Mapping LOAD "__FK_reporter", capitalize(replace([displayName_u0], '.', ' ')) as Reporter RESIDENT TempResults WHERE NOT IsNull([__FK_reporter]) ;
Issues: LOAD 1 as AllItemCount,
*, Date(Timestamp([Created Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueCreateDate, Timestamp([Created Date Time], 'YYYY-MM-DD hh:mm:ss') AS IssueCreateDateTime, Timestamp([Resolution Date Time], 'YYYY-MM-DD hh:mm:ss') AS IssueResolutionDateTime, Date(Timestamp([Updated Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueUpdateDate, //Date(Timestamp(DUEDATE, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueDueDate, Date(Timestamp([Resolution Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueResolutionDate, Date(Timestamp([Due Date], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueDueDate,
Floor(Now() - [Created Date Time]) as [Days Since Created] //where WildMatch([Issue Key],$(vKey)) ; LOAD [__KEY_fields] as IssueJSONKey, AutoNumber([__KEY_fields]) as Auto2, ApplyMap('IssueID', [__KEY_fields], -1) as IssueID, ApplyMap('IssueKey', [__KEY_fields], 'Unknown') as [Issue Key],
ApplyMap('IssueType', [__KEY_fields], -1) as [Issue Type], ApplyMap('priority', [__KEY_fields], Dual('n/k',99)) as Priority, ApplyMap('Creater', [__KEY_fields], 'Not Specified') as Creator,
ApplyMap('assignee', [__KEY_fields], 'Not Specified') as Assignee, ApplyMap('reporter', [__KEY_fields], 'Not Specified') as Reporter, ApplyMap('status', [__KEY_fields], dual('Not Specified',99)) as Status,
ApplyMap('resolution', [__KEY_fields], 'Unresolved') as Resolution, //[description_u14] as [Issue Description], //[summary_u3] as [Issue Summary], $(vDateFmt(created)) as [Created Date Time], Date(RangeMax( $(vDateFmt(lastViewed)), $(vDateFmt(created)), $(vDateFmt(updated)), $(vDateFmt(resolutiondate))) , 'DD MMM YYYY hh:mm') as [Last Viewed Date Time], // assume if updated or resolved it was viewed at the time $(vDateFmt(updated)) as [Updated Date Time], $(vDateFmt(duedate)) as [Due Date], $(vDateFmt(resolutiondate)) as [Resolution Date Time],
[timespent]/3600 as IssueTimeSpent, [aggregatetimespent]/3600 as [Time Spent Agg], [workratio] as [Work Ratio], [timeestimate]/3600 as IssueWorklog, [aggregatetimeoriginalestimate]/3600 as [Time Estimate Agg], [timeoriginalestimate]/3600 as IssueOrgTimeEstimate, [aggregatetimeestimate]/3600 as [Time Remaining Agg]
RESIDENT TempResults WHERE (not IsNull("__KEY_fields")) ;
///$tab Load Other Tables IssueType: LOAD DISTINCT [id] as IssueTypeID, [id] as IssueTypeUniqueKey, [name] as [Issue Type], [description] as [Issue Type Desc], [subtask] as [Issue Type Is SubTask], avatarId as [Issue Type Avatar]
RESIDENT TempResults WHERE (not IsNull("__FK_issuetype")) ;
///$tab Get Min ID tmpMinID: LOAD Min(IssueID) as MinID RESIDENT Issues ;
let vMinID = alt(peek('MinID', -1, 'tmpMinID'),0);
DROP TABLE tmpMinID; ///$tab End Loop DROP TABLE TempResults;
// Increment the page count let iPage = iPage + 1; loop
sorry , its only working once but now am getting the same error every time . my final script is given below
//LIB CONNECT TO 'Jira Server Rest API 2 (idainfront_ajave)'; LIB CONNECT TO 'JiraCloudForALL (idainfront_ajave)'; let vLastLoadStart = now(); let vNoOfRows = 0; let vVersionNumber = 1.00;
let vLoadAll = 0; let vLoadDays = 5;
set HidePrefix = '%';
let vData = 'lib://QVDs/';
// Set your filter number here
set vProject='SIDA';
set HidePrefix = '%';
let vData = 'lib://QVDs/';
// Set your filter number here let iFilter = 10010;
do while (iPage = 0 or (vMinID <> vLastMin)) and iPage < 300
let vLastMin = vMinID;
// if(vMinID=vLastMin) then // ;
// Set the less than code, or blank it for the first pass let vLessThanID = if(vMinID = 0, '', 'AND id < ' & vMinID);
TempResults:
LOAD * ; SQL SELECT "__KEY_root", (SELECT "expand", "id" AS "id_u6", "self" AS "self_u10", "key" AS "key_u4", "__KEY_issues", "__FK_issues", (SELECT "customfield_10070", "timespent", "customfield_11520", "aggregatetimespent", "customfield_11124", "customfield_11125", "customfield_11720", "customfield_11126", "customfield_11521", "customfield_10820", "resolutiondate", "workratio", "lastViewed", "customfield_10061", "created", "customfield_10141", "customfield_10220", "customfield_12520", "customfield_11821", "customfield_11623", "customfield_11820", "customfield_11622", "customfield_11625", "customfield_11822", "customfield_11624", "timeestimate", "aggregatetimeoriginalestimate", "assignee", "updated", "timeoriginalestimate", "description" AS "description_u3", "customfield_11220", "customfield_11221", "customfield_11421", "customfield_11420", "customfield_11621", "customfield_11620", "customfield_10523", "customfield_10920", "customfield_10921", "customfield_10922", "aggregatetimeestimate", "customfield_10923", "summary", "customfield_11131", "customfield_11132", "customfield_12420", "customfield_10000", "customfield_10320", "customfield_10321", "customfield_11127", "customfield_11920", "customfield_11128", "customfield_11721", "customfield_11129", "customfield_11723", "duedate", "customfield_10190", "resolution", "customfield_10113", "customfield_10114", "customfield_10822", "customfield_10420", "customfield_10160", "__KEY_fields", "__FK_fields", (SELECT "self", "id", "description", "iconUrl", "name", "subtask", "avatarId", "__FK_issuetype" FROM "issuetype" FK "__FK_issuetype"), (SELECT "self" AS "self_u1", "id" AS "id_u1", "key", "name" AS "name_u1", "projectTypeKey", "__KEY_project", "__FK_project", (SELECT "48x48", "24x24", "16x16", "32x32", "__FK_avatarUrls" FROM "avatarUrls" FK "__FK_avatarUrls"), (SELECT "self" AS "self_u0", "id" AS "id_u0", "description" AS "description_u0", "name" AS "name_u0", "__FK_projectCategory" FROM "projectCategory" FK "__FK_projectCategory") FROM "project" PK "__KEY_project" FK "__FK_project"), (SELECT "@Value", "__FK_fixVersions" FROM "fixVersions" FK "__FK_fixVersions" ArrayValueAlias "@Value"), (SELECT "self" AS "self_u2", "id" AS "id_u2", "description" AS "description_u1", "name" AS "name_u2", "__FK_resolution" FROM "resolution" FK "__FK_resolution"), (SELECT "self" AS "self_u3", "watchCount", "isWatching", "__FK_watches" FROM "watches" FK "__FK_watches"), (SELECT "@Value" AS "@Value_u0", "__FK_customfield_10420" FROM "customfield_10420" FK "__FK_customfield_10420" ArrayValueAlias "@Value_u0"), (SELECT "@Value" AS "@Value_u1", "__FK_labels" FROM "labels" FK "__FK_labels" ArrayValueAlias "@Value_u1"), (SELECT "@Value" AS "@Value_u2", "__FK_versions" FROM "versions" FK "__FK_versions" ArrayValueAlias "@Value_u2"), (SELECT "@Value" AS "@Value_u3", "__FK_issuelinks" FROM "issuelinks" FK "__FK_issuelinks" ArrayValueAlias "@Value_u3"), (SELECT "self" AS "self_u5", "description" AS "description_u2", "iconUrl" AS "iconUrl_u0", "name" AS "name_u4", "id" AS "id_u4", "__KEY_status", "__FK_status", (SELECT "self" AS "self_u4", "id" AS "id_u3", "key" AS "key_u0", "colorName", "name" AS "name_u3", "__FK_statusCategory" FROM "statusCategory" FK "__FK_statusCategory") FROM "status" PK "__KEY_status" FK "__FK_status"), (SELECT "@Value" AS "@Value_u4", "__FK_components" FROM "components" FK "__FK_components" ArrayValueAlias "@Value_u4"), (SELECT "self" AS "self_u6", "name" AS "name_u5", "key" AS "key_u1", "emailAddress", "displayName", "active", "timeZone", "__KEY_creator", "__FK_creator", (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 "creator" PK "__KEY_creator" FK "__FK_creator"), (SELECT "@Value" AS "@Value_u5", "__FK_customfield_10081" FROM "customfield_10081" FK "__FK_customfield_10081" ArrayValueAlias "@Value_u5"), (SELECT "@Value" AS "@Value_u6", "__FK_subtasks" FROM "subtasks" FK "__FK_subtasks" ArrayValueAlias "@Value_u6"), (SELECT "self" AS "self_u7", "name" AS "name_u6", "key" AS "key_u2", "emailAddress" AS "emailAddress_u0", "displayName" AS "displayName_u0", "active" AS "active_u0", "timeZone" AS "timeZone_u0", "__KEY_reporter", "__FK_reporter", (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 "reporter" PK "__KEY_reporter" FK "__FK_reporter"), (SELECT "progress", "total", "__FK_aggregateprogress" FROM "aggregateprogress" FK "__FK_aggregateprogress"), (SELECT "progress" AS "progress_u0", "total" AS "total_u0", "__FK_progress" FROM "progress" FK "__FK_progress"), (SELECT "self" AS "self_u8", "name" AS "name_u7", "key" AS "key_u3", "emailAddress" AS "emailAddress_u1", "displayName" AS "displayName_u1", "active" AS "active_u1", "timeZone" AS "timeZone_u1", "__KEY_assignee", "__FK_assignee", (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 "assignee" PK "__KEY_assignee" FK "__FK_assignee"), (SELECT "self" AS "self_u9", "iconUrl" AS "iconUrl_u1", "name" AS "name_u8", "id" AS "id_u5", "__FK_priority" FROM "priority" FK "__FK_priority") FROM "fields" PK "__KEY_fields" FK "__FK_fields") FROM "issues" PK "__KEY_issues" FK "__FK_issues") FROM JSON (wrap on) "root" PK "__KEY_root" QDL WITH CONNECTION ( QUERY "jql" "project=$(vProject) $(vLessThanID) ORDER BY id DESC", QUERY "maxResults" "1000" ) ;
Map_Reporter$(iPage): MAPPING LOAD "__FK_reporter", capitalize(replace([displayName_u0], '.', ' ')) as rptr RESIDENT TempResults WHERE (not IsNull("__FK_reporter")) ;
// Creater: // Mapping LOAD // "__FK_reporter", // capitalize(replace([displayName], '.', ' ')) as Creater // RESIDENT TempResults // WHERE NOT IsNull([__FK_reporter]) ;
Issues: LOAD 1 as AllItemCount,
*,
//ApplyMap('Map_Epic', [Epic Code]) as Epic, Date(Timestamp([Created Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueCreateDate, Timestamp([Created Date Time], 'YYYY-MM-DD hh:mm:ss') AS IssueCreateDateTime, Timestamp([Resolution Date Time], 'YYYY-MM-DD hh:mm:ss') AS IssueResolutionDateTime, Date(Timestamp([Updated Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueUpdateDate, //Date(Timestamp(DUEDATE, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueDueDate, Date(Timestamp([Resolution Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueResolutionDate, Date(Timestamp([Due Date], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueDueDate,
Floor(Now() - [Created Date Time]) as [Days Since Created] ; LOAD [__KEY_fields] as IssueJSONKey, ApplyMap('Map_IssueID$(iPage)', [__KEY_fields], -1) as IssueID, ApplyMap('Map_IssueKey$(iPage)', [__KEY_fields], 'Unknown') as [Issue Key], ApplyMap('Map_IssueType$(iPage)', [__KEY_fields], -1) as [Issue Type], ApplyMap('Map_Priority$(iPage)', [__KEY_fields], Dual('n/k',99)) as Priority,
ApplyMap('Map_Assignee$(iPage)', [__KEY_fields], 'Not Specified') as Assignee, ApplyMap('Map_Reporter$(iPage)', [__KEY_fields], 'Not Specified') as Reporter, ApplyMap('Map_Status$(iPage)', [__KEY_fields], dual('Not Specified',99)) as Status, ApplyMap('Map_Resolution$(iPage)', [__KEY_fields], 'Unresolved') as Resolution,
[description_u3] as [Issue Description], [summary] as [Issue Summary], $(vDateFmt(created)) as [Created Date Time], Date(RangeMax( $(vDateFmt(lastViewed)), $(vDateFmt(created)), $(vDateFmt(updated)), $(vDateFmt(resolutiondate))) , 'DD MMM YYYY hh:mm') as [Last Viewed Date Time], // assume if updated or resolved it was viewed at the time $(vDateFmt(updated)) as [Updated Date Time], $(vDateFmt(duedate)) as [Due Date], $(vDateFmt(resolutiondate)) as [Resolution Date Time], [timespent]/3600 as IssueTimeSpent, [aggregatetimespent]/3600 as [Time Spent Agg], [workratio] as [Work Ratio], [timeestimate]/3600 as IssueWorklog, [aggregatetimeoriginalestimate]/3600 as [Time Estimate Agg], [timeoriginalestimate]/3600 as IssueOrgTimeEstimate, [aggregatetimeestimate]/3600 as [Time Remaining Agg],
$(vLoadedField) as [Issue Refreshed] RESIDENT TempResults WHERE (not IsNull("__KEY_fields")) ;
///$tab Load Other Tables IssueType: LOAD DISTINCT [id] as IssueTypeID, [id] as IssueTypeUniqueKey, [name] as [Issue Type], [description] as [Issue Type Desc], [subtask] as [Issue Type Is SubTask], avatarId as [Issue Type Avatar]
RESIDENT TempResults WHERE (not IsNull("__FK_issuetype")) ;
///$tab Get Min ID tmpMinID: LOAD Min(IssueID) as MinID RESIDENT Issues ;
let vMinID = alt(peek('MinID', -1, 'tmpMinID'),0);
Trace vMinID : $(vMinID);
DROP TABLE tmpMinID; ///$tab End Loop DROP TABLE TempResults;
// Increment the page count let iPage = iPage + 1; loop
Thanks for your help . script is working perfectly fine now .
currently am fetching the records using the filter name project and max result 1000 which is working as I want with specific project .
I want to make it dynamic so that I can fetch all the issues for all the projects . so I used one for loop before the do while loop and pass the variable in project filter but last project will overwrite all the records and am not getting all the issues related to all projects that I passed to the filter
could you please suggest how can I do that to get all the records for all the projects .
for example I have 3 projects A,B,C
if A has 10 issues , B has 5 Issues and C has 7 issues so I want total 22 (A+B+C) in issues table
script which I used is given below
LIB CONNECT TO 'abc';
//set vProject='UTVITSY'; set vProject='QS';
let vData = 'lib://SenseData/1.QVD/JiraCloudNew/Extract/';
// Set the less than code, or blank it for the first pass let vLessThanID = if(vMinID = 0, '', 'AND id < ' & vMinID);
//Exit Script TempResults:
SQL SELECT "expand" AS "expand_u0", "startAt", "maxResults", "total" AS "total_u1", "__KEY_root", (SELECT "expand", "id" AS "id_u7", "self" AS "self_u12", "key" AS "key_u5", "__KEY_issues", "__FK_issues", (SELECT "customfield_10070", "timespent", "customfield_11520", "aggregatetimespent", "resolution", "customfield_11124", "customfield_11125", "customfield_11720", "customfield_10114", "customfield_11126", "customfield_11521", "customfield_10822", "resolutiondate", "workratio", "lastViewed", "customfield_10060", "customfield_10061", "created", "customfield_10220", "customfield_10420", "customfield_12520", "customfield_11623", "customfield_11821", "customfield_11622", "customfield_11820", "customfield_11625", "customfield_11624", "customfield_11822", "timeestimate", "aggregatetimeoriginalestimate", "assignee", "updated", "timeoriginalestimate", "description" AS "description_u4", "customfield_10010", "customfield_11220", "customfield_11221", "customfield_11421", "customfield_11420", "customfield_11621", "customfield_11620", "customfield_10523", "customfield_10920", "customfield_10921", "customfield_10922", "aggregatetimeestimate", "customfield_10923", "summary", "customfield_12220", "customfield_12022", "customfield_11131", "customfield_11132", "customfield_12420", "customfield_10000", "customfield_12023", "customfield_10320", "customfield_11127", "customfield_11920", "customfield_11128", "customfield_11721", "customfield_11129", "customfield_11723", "duedate", "customfield_12120", "__KEY_fields", "__FK_fields", (SELECT "self", "id", "description", "iconUrl", "name", "subtask", "avatarId", "__FK_issuetype" FROM "issuetype" FK "__FK_issuetype"), (SELECT "self" AS "self_u1", "id" AS "id_u1", "key", "name" AS "name_u1", "projectTypeKey", "__KEY_project", "__FK_project", (SELECT "48x48", "24x24", "16x16", "32x32", "__FK_avatarUrls" FROM "avatarUrls" FK "__FK_avatarUrls"), (SELECT "self" AS "self_u0", "id" AS "id_u0", "description" AS "description_u0", "name" AS "name_u0", "__FK_projectCategory" FROM "projectCategory" FK "__FK_projectCategory") FROM "project" PK "__KEY_project" FK "__FK_project"), (SELECT "self" AS "self_u2", "watchCount", "isWatching", "__FK_watches" FROM "watches" FK "__FK_watches"), (SELECT "self" AS "self_u3", "iconUrl" AS "iconUrl_u0", "name" AS "name_u2", "id" AS "id_u2", "__FK_priority" FROM "priority" FK "__FK_priority"), (SELECT "@Value", "__FK_labels" FROM "labels" FK "__FK_labels" ArrayValueAlias "@Value"), (SELECT "@Value" AS "@Value_u0", "__FK_issuelinks" FROM "issuelinks" FK "__FK_issuelinks" ArrayValueAlias "@Value_u0"), (SELECT "self" AS "self_u5", "description" AS "description_u1", "iconUrl" AS "iconUrl_u1", "name" AS "name_u4", "id" AS "id_u4", "__KEY_status", "__FK_status", (SELECT "self" AS "self_u4", "id" AS "id_u3", "key" AS "key_u0", "colorName", "name" AS "name_u3", "__FK_statusCategory" FROM "statusCategory" FK "__FK_statusCategory") FROM "status" PK "__KEY_status" FK "__FK_status"), (SELECT "self" AS "self_u6", "id" AS "id_u5", "description" AS "description_u2", "name" AS "name_u5", "__FK_security" FROM "security" FK "__FK_security"), (SELECT "self" AS "self_u7", "name" AS "name_u6", "key" AS "key_u1", "emailAddress", "displayName", "active", "timeZone", "__KEY_creator", "__FK_creator", (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 "creator" PK "__KEY_creator" FK "__FK_creator"), (SELECT "@Value" AS "@Value_u1", "__FK_customfield_10081" FROM "customfield_10081" FK "__FK_customfield_10081" ArrayValueAlias "@Value_u1"), (SELECT "@Value" AS "@Value_u2", "__FK_subtasks" FROM "subtasks" FK "__FK_subtasks" ArrayValueAlias "@Value_u2"), (SELECT "self" AS "self_u8", "name" AS "name_u7", "key" AS "key_u2", "emailAddress" AS "emailAddress_u0", "displayName" AS "displayName_u0", "active" AS "active_u0", "timeZone" AS "timeZone_u0", "__KEY_reporter", "__FK_reporter", (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 "reporter" PK "__KEY_reporter" FK "__FK_reporter"), (SELECT "progress", "total", "__FK_aggregateprogress" FROM "aggregateprogress" FK "__FK_aggregateprogress"), (SELECT "progress" AS "progress_u0", "total" AS "total_u0", "__FK_progress" FROM "progress" FK "__FK_progress"), (SELECT "self" AS "self_u9", "name" AS "name_u8", "key" AS "key_u3", "emailAddress" AS "emailAddress_u1", "displayName" AS "displayName_u1", "active" AS "active_u1", "timeZone" AS "timeZone_u1", "__KEY_assignee", "__FK_assignee", (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 "assignee" PK "__KEY_assignee" FK "__FK_assignee"), (SELECT "self" AS "self_u10", "id" AS "id_u6", "description" AS "description_u3", "name" AS "name_u9", "__FK_resolution" FROM "resolution" FK "__FK_resolution"), (SELECT "self" AS "self_u11", "name" AS "name_u10", "key" AS "key_u4", "emailAddress" AS "emailAddress_u2", "displayName" AS "displayName_u2", "active" AS "active_u2", "timeZone" AS "timeZone_u2", "__KEY_customfield_11126", "__FK_customfield_11126", (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") FROM "customfield_11126" PK "__KEY_customfield_11126" FK "__FK_customfield_11126") FROM "fields" PK "__KEY_fields" FK "__FK_fields") FROM "issues" PK "__KEY_issues" FK "__FK_issues") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION ( QUERY "jql" "project=$(vProject_Name) $(vLessThanID) ORDER BY id DESC", QUERY "maxResults" "1000" ) ;
Map_Reporter$(iPage): MAPPING LOAD "__FK_reporter", capitalize(replace([displayName_u0], '.', ' ')) as rptr RESIDENT TempResults WHERE (not IsNull("__FK_reporter")) ;
Map_Security$(iPage): MAPPING LOAD "__FK_security", [name_u5] as Sec RESIDENT TempResults WHERE (not IsNull("__FK_security")) ;
Map_Project$(iPage): Mapping LOAD "__FK_project", [name_u1] as ProjectName RESIDENT TempResults WHERE NOT IsNull([__FK_project]);
Issues: LOAD 1 as AllItemCount,
*,
//ApplyMap('Map_Epic', [Epic Code]) as Epic, Date(Timestamp([Created Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueCreateDate, Timestamp([Created Date Time], 'YYYY-MM-DD hh:mm:ss') AS IssueCreateDateTime, Timestamp([Resolution Date Time], 'YYYY-MM-DD hh:mm:ss') AS IssueResolutionDateTime, Date(Timestamp([Updated Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueUpdateDate, //Date(Timestamp(DUEDATE, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueDueDate, Date(Timestamp([Resolution Date Time], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueResolutionDate, Date(Timestamp([Due Date], 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') AS IssueDueDate, Floor(Now() - [Created Date Time]) as [Days Since Created] WHERE NOT EXISTS ([Issue Key]) ; LOAD [__KEY_fields] as IssueJSONKey, ApplyMap('Map_IssueID$(iPage)', [__KEY_fields], -1) as IssueID, ApplyMap('Map_IssueKey$(iPage)', [__KEY_fields], 'Unknown') as [Issue Key], ApplyMap('Map_IssueType$(iPage)', [__KEY_fields], -1) as [Issue Type], ApplyMap('Map_Priority$(iPage)', [__KEY_fields], Dual('n/k',99)) as Priority,
ApplyMap('Map_Assignee$(iPage)', [__KEY_fields], 'Not Specified') as Assignee, ApplyMap('Map_Reporter$(iPage)', [__KEY_fields], 'Not Specified') as Reporter, ApplyMap('Map_Status$(iPage)', [__KEY_fields], dual('Not Specified',99)) as Status,
ApplyMap('Map_Resolution$(iPage)', [__KEY_fields], 'Unresolved') as Resolution, ApplyMap('Map_Security$(iPage)', [__KEY_fields], 'Unresolved') as SecurityLevel, ApplyMap('Map_Project$(iPage)', [__KEY_fields], 'Unresolved') as ProjectName,
[description] as [Issue Description], [summary] as [Issue Summary], $(vDateFmt(created)) as [Created Date Time], Date(RangeMax( $(vDateFmt(lastViewed)), $(vDateFmt(created)), $(vDateFmt(updated)), $(vDateFmt(resolutiondate))) , 'DD MMM YYYY hh:mm') as [Last Viewed Date Time], // assume if updated or resolved it was viewed at the time $(vDateFmt(updated)) as [Updated Date Time], $(vDateFmt(duedate)) as [Due Date], $(vDateFmt(resolutiondate)) as [Resolution Date Time],
[timespent]/3600 as IssueTimeSpent, [aggregatetimespent]/3600 as [Time Spent Agg], [workratio] as [Work Ratio], [timeestimate]/3600 as IssueWorklog, [aggregatetimeoriginalestimate]/3600 as [Time Estimate Agg], [timeoriginalestimate]/3600 as IssueOrgTimeEstimate, [aggregatetimeestimate]/3600 as [Time Remaining Agg]
RESIDENT TempResults WHERE (not IsNull("__KEY_fields")) ;
tmpMinID: LOAD Min(IssueID) as MinID RESIDENT Issues ;
let vMinID = alt(peek('MinID', -1, 'tmpMinID'),0);
DROP TABLE tmpMinID;
DROP TABLE TempResults;
// Increment the page count let iPage = iPage + 1; loop
IssuesFinal: Load *,'$(vProject_Name)' as PName Resident Issues;
Drop Table Issues;
trace vProject_Name :$(vProject_Name); trace Pro :$(Pro); // let Pro = Pro + 1; // loop
Next Pro
STORE IssuesFinal INTO [$(vData)JIRA_Issues.qvd] (qvd);
Make sure you re-initialise all variables (e.g. Page and MaxID) each time you get to a new project.
I would move the write to QVD inside the project loop, and suffix it (e.g. JIRA_Issues_$(Pro).qvd) so you can check each independently.
You will want to ensure that the mapping tables map to the right place, suggest adding the project name to the mapping table names (e.g. Map_$(Pro)_IssueType$(iPage)).
Ensure all other tables are dropped before next iteration of the project loop.
Thanks for your quick response . If you can see my code I am initializing all the variable on every new project but still its overwriting the records i dont know why .
I appreciate if possible please see my code and suggest the write way of doing this
