Qlik Community

Qlik Sense Documents & Videos

Documents & videos about Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now

How to show the total Time of Jira Epic and its related Issues in Qliksense

Partner
Partner

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

  • Jira Issue
  • Issuelink
  • IssueType
  • Project
  • Issuetype

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.

mJiraIssueType:

Mapping LOAD

                ID,

     pname as issuetype

FROM

[$(QVDExtractPath)/JIRA_issuetype.qvd]

(qvd);

//******************************** Below code to fetch the Project Key

ProjectKeyEpic:

mapping LOAD

               ID AS %ProjectID,  

    pkey AS %ProjectKey

FROM

[$(QVDExtractPath)/JIRA_project.qvd]

(qvd);

 

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

LOAD   

    LINKTYPE,

    SOURCE as %IssueID,

    DESTINATION,

    ApplyMap('LinkName',LINKTYPE ,'Unknown') as LINKNAME,

    ApplyMap('JiraProject',DESTINATION,'') as %ProjectID  // Based on actual Issue in Jira issue link qvd   

FROM [lib://SenseData/1.QVD/1.Extract/JIRA_issuelink.qvd]

(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

 

Inner Join(IssueLink)

LOAD

    ID as DESTINATION,

    issuenum as EpicIssues,

    SUMMARY as ISSUES_IN_EPIC   

FROM [lib://SenseData/1.QVD/1.Extract/JIRA_jiraissue.qvd]

(qvd);

 

Step6 – Load the project table to fetch the project name and Issuekey of the Epic and related projects

 

TableTmp:

 

Load

               ID AS %ProjectID,

               pkey AS %ProjectKey

FROM

[$(QVDExtractPath)/JIRA_project.qvd]

(qvd);

 

Left Join(TableTmp)

Load

               %ProjectID,

    %IssueID,

    %IssueKey,   

    ProjectKeyEpic,

    IssueType

Resident JiraIssue_Temp;

 

Left Join (JiraIssue_Temp)

Load

    %IssueID,

    %ProjectKey as  %ProjectKey_Temp,   

    if(WildMatch(IssueType,'Epic'),Upper(ProjectKeyEpic&'-'&%IssueKey),Upper(%ProjectKey&'-'&%IssueKey)) as %ProjectJiraIssueKey

Resident TableTmp;

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

 

JiraEpic:

Load *, Upper(%ProjectKey_Temp &'-'& EpicIssues) as %ProjectIssueKey , 'Jira' as Flag Resident JiraIssue_Temp ;

//******************************** Below concatination is used to add Epic Issue into the

Jiralist ********************

 

Concatenate

 

JiraEpic:

Load %IssueID,IssueType,

        IssueSummary ,

      %ProjectJiraIssueKey  ,

      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 .

 

Thanks and Happy Qliking

Ajay

 

Labels (2)
Version history
Revision #:
1 of 1
Last update:
2 weeks ago
Updated by:
 
Contributors