Create a gist now

Instantly share code, notes, and snippets.

anonymous /gist:c11e835ab15c8bdafa0f
Created May 29, 2015

What would you like to do?
Simple QlikView/Qlik Sense load script for JIRA using QVSource
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
/*******************************************************************
CHANGE LOG
1.0.0 - 29/05/15
----------------
* Initial release.
SETUP
In order to use this simple starter app for JIRA you should first
ensure that you have entered your JIRA base url, username and
password in the connector in the QVSource user interface.
You can then review the config section below, although in most
cases you should not need to make any changes.
IMPORTANT: If you are using Qlik Sense, please also ensure you
have read this page:
http://wiki.qvsource.com/Disabling-Standard-Mode-In-Qlik-Sense.ashx
*******************************************************************/
/*******************************************************************
CONFIG SECTION START
*******************************************************************/
//
// You can leave this value unless you have multiple apps reloading
// in parallel. It will be used in error logs to help identify which
// QlikView or Qlik Sense application was loading at the time.
//
let vAppId = 'JIRADemo';
//
// You can set this to an integer if you would like to first just
// test the app with a small number of items.
//
let vMaxIssues = '';
//
// This would be needed if using QVSource Web Edition in remote
// access mode. Otherwise just leave blank.
//
let vLoadAccessToken = '';
//
// You may need to update the machine name if you are using
// QVSource Web Edition in remote access mode oor using a different
// port.Otherwise just leave.
//
let vQVSourceURL = 'http://localhost:5555/QVSource';
/*******************************************************************
CONFIG SECTION END
*******************************************************************/
JIRAConnector_AllIssues:
LOAD
expand as AllIssues_expand,
id as AllIssues_id,
self as AllIssues_self,
key as AllIssues_key,
fields_issuetype_self as AllIssues_fields_issuetype_self,
fields_issuetype_id as AllIssues_fields_issuetype_id,
fields_issuetype_description as AllIssues_fields_issuetype_description,
fields_issuetype_iconUrl as AllIssues_fields_issuetype_iconUrl,
fields_issuetype_name as AllIssues_fields_issuetype_name,
fields_issuetype_subtask as AllIssues_fields_issuetype_subtask,
fields_timespent as AllIssues_fields_timespent,
fields_project_self as AllIssues_fields_project_self,
fields_project_id as AllIssues_fields_project_id,
fields_project_key as AllIssues_fields_project_key,
fields_project_name as AllIssues_fields_project_name,
fields_project_avatarUrls as AllIssues_fields_project_avatarUrls,
fields_aggregatetimespent as AllIssues_fields_aggregatetimespent,
fields_resolution as AllIssues_fields_resolution,
fields_resolutiondate as AllIssues_fields_resolutiondate,
fields_workratio as AllIssues_fields_workratio,
fields_lastViewed as AllIssues_fields_lastViewed,
fields_watches_self as AllIssues_fields_watches_self,
fields_watches_watchCount as AllIssues_fields_watches_watchCount,
fields_watches_isWatching as AllIssues_fields_watches_isWatching,
fields_created as AllIssues_fields_created,
fields_customfield_10020 as AllIssues_fields_customfield_10020,
fields_customfield_10021 as AllIssues_fields_customfield_10021,
fields_customfield_10022 as AllIssues_fields_customfield_10022,
fields_priority_self as AllIssues_fields_priority_self,
fields_priority_iconUrl as AllIssues_fields_priority_iconUrl,
fields_priority_name as AllIssues_fields_priority_name,
fields_priority_id as AllIssues_fields_priority_id,
fields_customfield_10023 as AllIssues_fields_customfield_10023,
fields_customfield_10024 as AllIssues_fields_customfield_10024,
fields_customfield_10016 as AllIssues_fields_customfield_10016,
fields_customfield_10017 as AllIssues_fields_customfield_10017,
fields_customfield_10018 as AllIssues_fields_customfield_10018,
fields_customfield_10019 as AllIssues_fields_customfield_10019,
fields_timeestimate as AllIssues_fields_timeestimate,
fields_aggregatetimeoriginalestimate as AllIssues_fields_aggregatetimeoriginalestimate,
fields_assignee as AllIssues_fields_assignee,
fields_updated as AllIssues_fields_updated,
fields_status_self as AllIssues_fields_status_self,
fields_status_description as AllIssues_fields_status_description,
fields_status_iconUrl as AllIssues_fields_status_iconUrl,
fields_status_name as AllIssues_fields_status_name,
fields_status_id as AllIssues_fields_status_id,
fields_status_statusCategory as AllIssues_fields_status_statusCategory,
fields_timeoriginalestimate as AllIssues_fields_timeoriginalestimate,
fields_description as AllIssues_fields_description,
fields_customfield_10012 as AllIssues_fields_customfield_10012,
fields_customfield_10013 as AllIssues_fields_customfield_10013,
fields_customfield_10014 as AllIssues_fields_customfield_10014,
fields_customfield_10015 as AllIssues_fields_customfield_10015,
fields_customfield_10005 as AllIssues_fields_customfield_10005,
fields_customfield_10006 as AllIssues_fields_customfield_10006,
fields_customfield_10007 as AllIssues_fields_customfield_10007,
fields_customfield_10008 as AllIssues_fields_customfield_10008,
fields_aggregatetimeestimate as AllIssues_fields_aggregatetimeestimate,
fields_summary as AllIssues_fields_summary,
fields_creator_self as AllIssues_fields_creator_self,
fields_creator_name as AllIssues_fields_creator_name,
fields_creator_key as AllIssues_fields_creator_key,
fields_creator_emailAddress as AllIssues_fields_creator_emailAddress,
fields_creator_avatarUrls as AllIssues_fields_creator_avatarUrls,
fields_creator_displayName as AllIssues_fields_creator_displayName,
fields_creator_active as AllIssues_fields_creator_active,
fields_creator_timeZone as AllIssues_fields_creator_timeZone,
fields_reporter_self as AllIssues_fields_reporter_self,
fields_reporter_name as AllIssues_fields_reporter_name,
fields_reporter_key as AllIssues_fields_reporter_key,
fields_reporter_emailAddress as AllIssues_fields_reporter_emailAddress,
fields_reporter_avatarUrls as AllIssues_fields_reporter_avatarUrls,
fields_reporter_displayName as AllIssues_fields_reporter_displayName,
fields_reporter_active as AllIssues_fields_reporter_active,
fields_reporter_timeZone as AllIssues_fields_reporter_timeZone,
fields_customfield_10000 as AllIssues_fields_customfield_10000,
fields_aggregateprogress_progress as AllIssues_fields_aggregateprogress_progress,
fields_aggregateprogress_total as AllIssues_fields_aggregateprogress_total,
fields_customfield_10001 as AllIssues_fields_customfield_10001,
fields_customfield_10002 as AllIssues_fields_customfield_10002,
fields_customfield_10003 as AllIssues_fields_customfield_10003,
fields_customfield_10004 as AllIssues_fields_customfield_10004,
fields_environment as AllIssues_fields_environment,
fields_duedate as AllIssues_fields_duedate,
fields_progress_progress as AllIssues_fields_progress_progress,
fields_progress_total as AllIssues_fields_progress_total,
fields_votes_self as AllIssues_fields_votes_self,
fields_votes_votes as AllIssues_fields_votes_votes,
fields_votes_hasVoted as AllIssues_fields_votes_hasVoted
FROM
[$(vQVSourceURL)/JIRAConnector/?table=AllIssues&maxResults=$(vMaxIssues)&appID=$(vAppId)&loadAccessToken=$(vLoadAccessToken)]
(qvx);
// IMPORTANT: If, when loading the above script, you receive a 'Script Error' Dialog box with a 'Field Not Found'
// (or other) error, the first thing you should do is copy and paste the full request URL (i.e. the URL between the square [...]
// brackets IN THE QLIKVIEW / QLIK SENSE SCRIPT ERROR DIALOG) into a browser address bar and check the response.
LET noIssues = NoOfRows('JIRAConnector_AllIssues');
set errormode = 0;
for i=0 to $(noIssues)-1
let vIssueId = peek('AllIssues_id', $(i), 'JIRAConnector_AllIssues');
trace 'Looking for history for issue $(vIssueId)';
JIRAConnector_History:
LOAD
'$(vIssueId)' as AllIssues_id,
id as History_id,
author_self as History_author_self,
author_name as History_author_name,
author_key as History_author_key,
author_emailAddress as History_author_emailAddress,
// author_avatarUrls_N_48x48 as History_author_avatarUrls_N_48x48,
// author_avatarUrls_N_24x24 as History_author_avatarUrls_N_24x24,
// author_avatarUrls_N_16x16 as History_author_avatarUrls_N_16x16,
author_avatarUrls_N_32x32 as History_author_avatarUrls_N_32x32,
author_displayName as History_author_displayName,
author_active as History_author_active,
author_timeZone as History_author_timeZone,
created as History_created,
items_field as History_items_field,
items_fieldtype as History_items_fieldtype,
items_from as History_items_from,
items_fromString as History_items_fromString,
items_to as History_items_to,
items_toString as History_items_toString
FROM
[$(vQVSourceURL)/JIRAConnector/?table=History&appID=$(vAppId)&issueIdOrKey=$(vIssueId)&loadAccessToken=$(vLoadAccessToken)]
(qvx);
trace 'Looking for comments for issue $(vIssueId)';
JIRAConnector_Comments:
LOAD
'$(vIssueId)' as AllIssues_id,
self as Comments_self,
id as Comments_id,
author_self as Comments_author_self,
author_name as Comments_author_name,
author_key as Comments_author_key,
author_emailAddress as Comments_author_emailAddress,
// author_avatarUrls_N_48x48 as Comments_author_avatarUrls_N_48x48,
// author_avatarUrls_N_24x24 as Comments_author_avatarUrls_N_24x24,
// author_avatarUrls_N_16x16 as Comments_author_avatarUrls_N_16x16,
author_avatarUrls_N_32x32 as Comments_author_avatarUrls_N_32x32,
author_displayName as Comments_author_displayName,
author_active as Comments_author_active,
author_timeZone as Comments_author_timeZone,
body as Comments_body,
updateAuthor_self as Comments_updateAuthor_self,
updateAuthor_name as Comments_updateAuthor_name,
updateAuthor_key as Comments_updateAuthor_key,
updateAuthor_emailAddress as Comments_updateAuthor_emailAddress,
// updateAuthor_avatarUrls_N_48x48 as Comments_updateAuthor_avatarUrls_N_48x48,
// updateAuthor_avatarUrls_N_24x24 as Comments_updateAuthor_avatarUrls_N_24x24,
// updateAuthor_avatarUrls_N_16x16 as Comments_updateAuthor_avatarUrls_N_16x16,
updateAuthor_avatarUrls_N_32x32 as Comments_updateAuthor_avatarUrls_N_32x32,
updateAuthor_displayName as Comments_updateAuthor_displayName,
updateAuthor_active as Comments_updateAuthor_active,
updateAuthor_timeZone as Comments_updateAuthor_timeZone,
created as Comments_created,
updated as Comments_updated
FROM
[$(vQVSourceURL)/JIRAConnector/?table=Comments&appID=$(vAppId)&issueIdOrKey=$(vIssueId)&loadAccessToken=$(vLoadAccessToken)]
(qvx);
// IMPORTANT: If, when loading the above script, you receive a 'Script Error' Dialog box with a 'Field Not Found'
// (or other) error, the first thing you should do is copy and paste the full request URL (i.e. the URL between the square [...]
// brackets IN THE QLIKVIEW / QLIK SENSE SCRIPT ERROR DIALOG) into a browser address bar and check the response.
next i

Could you please let me know how do I run this script in Qlik Sense (web or desktop). I am looking to connect to JIRA

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment