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.

[Qlik-Developer]

Jira:
LOAD
UserStory,
Sprint
FROM [lib://AttachedFiles/Sprints.xlsx]
(ooxml, embedded labels, table is Sheet1);

tmpSprints:
LOAD
UserStory,
subfield(Sprint, ']') as SprintText
RESIDENT Jira;

Sprints:
LOAD
UserStory,
mid(IdPair, index(IdPair, '=') + 1, 99) as Id,
mid(RapidViewPair, index(RapidViewPair, '=') + 1, 99) as RapidView,
mid(Namepair, index(Namepair, '=') + 1, 99) as Name

;
LOAD
UserStory,
subfield(SprintText, ',', 1) as IdPair,
subfield(SprintText, ',', 2) as RapidViewPair,
subfield(SprintText, ',', 4) as Namepair
RESIDENT tmpSprints;

DROP TABLE tmpSprints;

i have created Name filed

Value for name filed coming like above ,with sprint value Closed value also coming.

But i want output should be separate by comma in Name filed.

[stevedark]

Add this after what you have already...

Sprints2:
LOAD
UserStory,
CONCAT(DISTINCT Id, ', ') as Id,
CONCAT(DISTINCT RapidView, ', ') as RapidView,
CONCAT(DISTINCT Name, ', ') as Name
RESIDENT Sprints
GROUP BY UserStory;

DROP TABLE Sprints;

[Qlik-Developer]

in Sprint2 table distinct key word is not working showing error,

one more issue is Name field is coming wrong, with Sprint value Closed value is also coming.

written code for Name field in below

[stevedark]

Try removing the DISTINCT statements, they may not be required/allowed in the load script.

Just add a WHERE statement to the Sprints2 table of WHERE Name <> 'CLOSED'

[Qlik-Developer]

If  you see here Name field is giving only sprint 8 for userstory 10 nd sprint 9 for user story 11..

Where as user story 10 having sprint 8 , sprint9 nd user story 11 having sprint 9 , sprint 10.

Above code i have used is this correct? Then why instead of sprint coming status value CLOSED.

Please give me code how you did for Id and Rapidview.

subfield(SprintText, ',', 4) as Namepair

mid(Namepair, index(Namepair, '=') + 1, 99) as Name

[Qlik-Developer]

Its not just about Closed value is coming..

sprint8 and sprint 9 should come for user story 10 but sprint 8 and Closed values are coming

Sprint 9 and sprint 10 should come for  userstory 11 but sprint 9 and Closed values are coming under Name field.

Please give me code for sprint as well like how you did for Id and Rapidview

[stevedark]

Looks like there may be extra commas in some of the text block. If you pull subfield 4 and 5 into separate fields does it find the sprint in chunk 5 where CLOSED is in chunk 4?

You could try replacing the subfield code with this:

textbetween(SprintText, 'name=', ',') as Name,

Obviously, other fields can be obtained in the same way. The nice thing with this approach is that you don't have to go via the 'pair' fields and do the preceding load.

[Qlik-Developer]

Jira:
LOAD
UserStory,
Sprint
FROM [lib://AttachedFiles/Sprints.xlsx]
(ooxml, embedded labels, table is Sheet1);

tmpSprints:
LOAD
UserStory,
subfield(Sprint, ']') as SprintText
RESIDENT Jira;

Sprints:
LOAD
UserStory,

textbetween(SprintText, 'name=', ',') as Name
RESIDENT tmpSprints;

DROP TABLE tmpSprints;

Sprints2:
LOAD
UserStory,
CONCAT(DISTINCT Name, ', ') as Name
RESIDENT Sprints
GROUP BY UserStory;

DROP TABLE Sprints;

Finaly the code should be like this????

Do temsprints table required or sprints table can be resident from jira table?

[stevedark]

I've done it like this partly as I'm not sure if preceding load will work on all parts, and also partly so you can unit test. You could try going all in with the preceding load, but some parts may need splitting out.

See if this works:

Jira:
LOAD
  UserStory,
  CONCAT(DISTINCT Name, ', ') as Name
  GROUP BY UserStory
  ;
LOAD
  UserStory,
  textbetween(SprintText, 'name=', ',') as Name
  ;
LOAD
  UserStory,
  subfield(Sprint, ']') as SprintText
  ;
LOAD
  UserStory,
  Sprint
FROM [lib://AttachedFiles/Sprints.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

I honestly don't know if this will work or not, but obviously you can test it by doing each layer at a time.

Please post back and let me know your findings.

[Qlik-Developer]

Thank you it is working fine, but  i removed distinct form the last preceding load as it not allowing in load script, will  concatenation of  distinct Name will happen properly even after removing the distinct keyword?