Qlik Sense Rest API Connector for Jira/Getting custom fields with their names from jira to Qlik

[Qlik-Developer]

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.

Thanks in Advance!!!!!!!!!!!

Varsha.

[ajayvermaida]

@stevedark 

I appreciate if you can confirm  about the filter .

[ajayvermaida]

@Qlik-Developer @stevedark 

HI ,

I am also getting the below string containing sprint related fields . which I break it as desire but i dont know what is sequence is here ?

com.atlassian.greenhopper.service.sprint.Sprint@1a4b19ce[id=867,rapidViewId=183,state=CLOSED,name=IT 2020 52-53,startDate=2020-12-18T11:56:26.423+01:00,endDate=2021-01-01T11:56:00.000+01:00,completeDate=2021-01-29T12:10:48.328+01:00,activatedDate=2020-12-18T11:56:26.423+01:00,sequence=866,goal=,autoStartStop=false]

the issue is , that field contains the only sprint where any issue has been completed but if no issue completed in that sprint that sprint will not come into this filed .

My requirement is to fetch all sprint and associated issues also I have to show sprint wise issues suppose Issue 123 originally pick in Sprint abc but carry forward to sprint XYZ then both sprint will show the issue 123. 

can anyone help me to achieve this goal . 

[stevedark]

Hi @ajayvermaida 

You are probably going to want to set about that field with a subfield to chunk it up. Given that each sprint then has it's own set of data within that it will probably require a subfield on one delimiter and then a preceding load on that. I think I may have given some pointers on this to someone on Community before then, so you may find some code that is useful elsewhere.

Filling in data which isn't there is not always easy. Do you have the same list of sprints for all issues? If so it should be possible to fill in gaps with a composite key on Issue ID and Sprint ID and a WHERE NOT EXISTS on a temporary table created with a FULL OUTER JOIN on Issue ID and Sprint.

Good luck!

Steve

[ajayvermaida]

@Qlik-Developer 

Hi , Could you please share your rest api on which you get custom fields in parts for Sprint ?

[ajayvermaida]

@Qlik-Developer @stevedark 

Hi,

recently I am also working to extract all the issues and related sprint list  like @Qlik-Developer  required. after so much hit and trials and  spend time I found the solution with rest API .

Thanks to  @stevedark , based on his suggestion I have solve the issue and now I can achieve my requirement. I am giving my code below may be other one can take help with this

am using the below API to fetch the records here startAt is used for pagination and maxResults=- gives you 1000 records at one page

WITH CONNECTION(Url "https://jira.idainfront.se/rest/api/2/search/?jql=project=QS&startAt=$(iPage)&maxResults=-1") 

With help of above API you will get a table with others tables in jason script like below

(SELECT
"@Value" AS "@Value_u0",
"__FK_customfield_10420"
FROM "customfield_10420" FK "__FK_customfield_10420" ArrayValueAlias "@Value_u0")

@value contains all the sprint with associated fields like startdate , enddate, activateddate , reporviewid etc and thevalue stored like below format but conmtains full list of sprint with other important fields

com.atlassian.greenhopper.service.sprint.Sprint@1a7d6c2b[id=685,rapidViewId=255,state=CLOSED,name=Sprint 8 - w36-38,startDate=2020-09-01T09:26:18.994+02:00,endDate=2020-09-22T08:33:00.000+02:00,completeDate=2020-09-21T11:29:14.987+02:00,activatedDate=2020-09-01T09:26:18.994+02:00,sequence=685,goal=144 timmar 2020-09-01 - 2020-09-21 Lena+Peter + Lite bosse som byter med Lena,autoStartStop=false]

as @stevedark suggested in his script you have to map the issue_key with the sprint table and store in QVD like below 

//********************* Below code is maaping table for issuekey

Map_$(Pro)_$(vConName)_IssueKey$(iPage):
MAPPING LOAD
"__KEY_issues",
[key_u5]
RESIDENT TempResults
WHERE (not IsNull("__KEY_issues"))
;

SprintFinalFinal:

Load [__FK_customfield_10420] as [__KEY_fields],
[@Value_u0] as SprintSummary,
ApplyMap('Map_$(Pro)_$(vConName)_IssueKey$(iPage)',
[__FK_customfield_10420], 'Unknown') as %IssueKey,
'$(vConName)' as ConName10
RESIDENT TempResults
WHERE NOT IsNull([__FK_customfield_10420]);

STORE SprintFinalFinal INTO [$(vData)JIRA_Sprint.qvd] (qvd);

you get issue_key associated with all sprint records . once you store the sprint qvd you sprint.qvd look like below

You can use the below code to get separated the sprint summary field in sprint and other required fields

Load *,Date(SprintStartDate,'YYYY-MM-DD') as SprintStartDate1,
Date(SprintCompleteDate,'YYYY-MM-DD') as SprintCompleteDate1
where len(SprintName) > 0 and SprintName<>'Sprint1' and BoardID<>'<null>';
LOAD %IssueKey12 as %IssueKey,

SubField(SubField( SprintSummary,',',2),'=',2) as BoardID,
SubField(SubField(SprintSummary,',',1),'=',2) as %IssueSprintID,
SubField(SubField(SprintSummary,',',3),'=',2) as State,
SubField(SubField(SprintSummary,',',4),'=',2) as SprintName,
Date(Timestamp(SubField(SubField(SprintSummary,',',5),'=',2), 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss') as SprintStartDate,
Date(Timestamp(SubField(SubField(SprintSummary,',',6),'=',2), 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss') as SprintEndDate,
Date(Timestamp(SubField(SubField(SprintSummary,',',7),'=',2), 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss') as SprintCompleteDate,
Date(Timestamp(SubField(SubField(SprintSummary,',',8),'=',2), 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD hh:mm:ss') as ActivateDate

FROM [lib://SenseData/1.QVD/JiraCloudNew/Extract/JIRA_Sprint1.qvd]
(qvd) ;

no need to write nested if statement to convert multiple sprint column in 1 

if you required full script then let me know will try to provide.

sorry for the grammatical mistakes and happy qliking 

[stevedark]

Hi @ajayvermaida 

Glad you got this working, and thank you for sharing your code with the Community!

Steve

[ajayvermaida]

@stevedark 

Thanks you and appreciate your work . without your help it would not be possible .

[ajayvermaida]

Hi @stevedark 

Did you ever extract change history of the issue ? like different states of jira for example a jira start with To DO status then move to In Progress and then from In Progress to Done .

please suggest how I can extract history table from jira .

[stevedark]

This is not something that I've had to do, so I don't know what is possible. There is likely an endpoint in the API though.

If not you could build your own history by creating a unique key from the Issue ID and the Modified Date and then use an incremental load strategy to append versions of a ticket from a QVD where it is not at the same date as the most recently loaded.

Hope that makes sense?

Steve

[ajayvermaida]

@stevedark 

Do you know how to establish a link between master measure , Dimension and Master visualization ?

in one of the Qliksense app I have some master measure , dimension and master visualization .

I have to first extract the name of the master items then on selection of the master visualization name

I have to show the master dimension and measure which are used in selected master visualization .

some how I am able to extract the master items name in meta data but I am not able to establish a link between master measure , dimension and visualization  like climber control custom report .

Can anyone help me how to establish a link between master items ?

currently we are using climber custom report for this requirement but user dont want to use any extension and want to remove custom report from the app.

Thanks in advance