How to show the total Time of Jira Epic and its related Issues in Qliksense
Epic and Related issues:-
I am working to develop the dashboard with Jira in qliksense . One of the task is to analyse Epic and its related issues . although Epic itself a Issue but if you see the Epic in Jira tool you will see Epic has many issues related with it and you have to find out the total spent hours on selection of the Epic issue which shows total hours spent of all the issues which are related to Epic.
To achieve this requirement we have to develop a hierarchy with Jira issue and Project table .
Below tables I have used to achieve this
Step1 - Load Issue Table with fields which you required also perform the apply map to fetch the issue type from issuetype table and Project key from Project.
pname as issuetype
//******************************** Below code to fetch the Project Key
ID AS %ProjectID,
pkey AS %ProjectKey
Step2 – Load issuelink table and perform apply map to fetch the field linkname (which identify the issue type is epic , cause etc) from table linktype ( You can also perform inner join between issue link and linktype table on the basis of ID in linktype and LINKTYPE field in issuelink table)
Please check below
SOURCE as %IssueID,
ApplyMap('LinkName',LINKTYPE ,'Unknown') as LINKNAME,
ApplyMap('JiraProject',DESTINATION,'') as %ProjectID // Based on actual Issue in Jira issue link qvd
Step3 - Also fetch Project id from project table on the basis of DESTINATION in issuelink and issueid (ID) in Jira Issue table . we need the project id later .
Step4 - Perform inner join between Issue and issuelink table based on issueID (ID in Jira Issue table) and Source in issuelink table . it will gives you all the issues related to Epic .
Step5 - Perform inner join again with resultant table (Issue and Issuelink) with Jira issue table to find out issue summary of all the related Issues in Epic and Epic issue num.
Please check the code below
ID as DESTINATION,
issuenum as EpicIssues,
SUMMARY as ISSUES_IN_EPIC
Step6 – Load the project table to fetch the project name and Issuekey of the Epic and related projects
ID AS %ProjectID,
pkey AS %ProjectKey
Left Join (JiraIssue_Temp)
%ProjectKey as %ProjectKey_Temp,
if(WildMatch(IssueType,'Epic'),Upper(ProjectKeyEpic&'-'&%IssueKey),Upper(%ProjectKey&'-'&%IssueKey)) as %ProjectJiraIssueKey
Drop Table TableTmp;
The if condition below which is used above highlight in bold is used to identify the Full Issue ID (Key+Isueid) of Epic and its related issues. Please provide any name, in my case I give the name %ProjectJiraIssueKey.
"if(WildMatch(IssueType,'Epic'),Upper(ProjectKeyEpic&'-'&%IssueKey),Upper(%ProjectKey&'-'&%IssueKey)) as %ProjectJiraIssueKey"
Congrats after this step you have all the required information related to all the issues which are related to Epic .
If you don’t want to add the time which are in Epic issue then this is the last step but if you want to summing up the time associated to Epic issue itself then perform the below step .
Step7- Resident the above code which we have developed to associate the issues with Epic and concatenate the Epic issue with code below and create the QVD with Name Jira_Epic
Load *, Upper(%ProjectKey_Temp &'-'& EpicIssues) as %ProjectIssueKey , 'Jira' as Flag Resident JiraIssue_Temp ;
//******************************** Below concatination is used to add Epic Issue into the
Upper(ProjectKeyEpic&'-'&%IssueKey) as %ProjectIssueKey,
%ProjectJiraIssueKey as %ProjectEpicKey ,
'Epic' as Flag
Resident JiraIssue_Temp where match(IssueType,'Epic');
Drop Table JiraIssue_Temp;
Sorry for the grammatical mistake and Hope this will help you in analyse the Epic and related issues .