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]

Hi @stevedark ,

In QWC sprint field is splitting into  multiple columns with xml code prefix and siffix.

For example: one Key is having sprints values ,YBS Sprint5 and YBS sprint6,YBS sprint 7 etc its splitting into three  separate custom fields with XML code.

But in jira all the sprints values for the key is separated by comma values.

Custom flds for ex:

Table:

customfiled_10100,customfiled_10100_02,customfiled_10100_3,key

YBS sprint 5,YBS sprint 6,YBS sprint 7,   YSP-10

i have concatenated  custom fields in the below way,

customfiled_10100 &','&customfiled_10100_02&','&customfiled_10100_3 as Sprint

But the resultant is coming like below in sprint column,, extra commas are coming due to concatenation of all the custom fields .

when I extracted for YSP project I got 15 custom fields ,so I have concatenated all the 15 custom fields,so its returning 15 commas in the sprint field.

So how can i remove those extra commas form the sprint column?

[stevedark]

Hi @Qlik-Developer 

You need to add additional code around your concatenation, or replace out double commas at the end.

You could try:

replace([your existing code], ',,', ',')

Which would replace any double commas with single commas.

A better approach would be to check what is there already during the concatenation and only add commas if required:

customfiled_10100 &
if(customfiled_10100 <> '' and customfiled_10100_02 <> '', ',', '') &
customfiled_10100_02 &
if(customfiled_10100 &  customfiled_10100_02 <> '' and customfiled_10100_03 <> '', ',', '') &
&customfiled_10100_3 as Sprint

Not particularly pretty, but it should work.

Not sure if the spelling error of field in your code should be there, but I have replicated it in my code, just in case.

Hope that helps.

Steve

[Qlik-Developer]

Hi @stevedark ,

i have renamed 15 custom fields with Fld1, Fld2, Fld3 etc.... by doing textbetween() 

After that, I have concatenated those 15 fields and named as Sprint. 

finally, Sprint filed is resulting in the extra commas if there are not sprint values.

[stevedark]

Hi @Qlik-Developer 

You need to have the & ',' inside the bracket of the if.

For example:

if(not IsNull(Fld1), Fld1 & ', ', '') & 

This will just give you one rogue comma on the end.

You could deal with this in a preceding load, perhaps. Name this field tmpSprint and then do:

if(len(tmpSprint ) > 0, left(tmpSprint, len(trim(tmpSprint))-1, '') as Sprint

Hope that helps.

Steve

[Qlik-Developer]

Hi @stevedark ,

Thanks for your answers.

I will refresh data  every day into Qlik. my requirement is I need to store yesterdays Actual execution count y bcz when I refresh today data count for yesterdays one is changing bcz few of test cases may change their status and move to the execution date of today's in this case actual execution for yesterday is not capturing.

Ex: yesterday data:

Test case,status,executed on,test set

AA,Passed,01/02/2021,Finance

BB,Failed,05/02/2021,Finance

Cc,Blocked,07/02/2021,Product

Dd,Pending,07/02/2021,Documents

Ee, Not completed,09/02/2021, Finance

Ff, Passed,10/02/2021, Product

Gg,Blocked,11/02/2021, Product

Hh, Failed,12/02/2021, Product

Ii,Pending,15/02/2021,Finance

Jj, Blocked,16/02/2021,Documents

Kk,Pending,16/02/2021,Documents

Ll,Passed,16/02/2021,product

Mm,no run,,Product

for example when I refresh data is on 16th feb is above,for 16th Actual execution Test cases are 9(from executed on start date to till 16th feb)

rangesum(above(count({<[ status]={'Passed','Failed','Not Completed','Pending'}>}
[Test case])

Ex: Todays data:

Test case,status,executed on,test set

AA,Passed,01/02/2021,Finance

BB,Failed,05/02/2021,Finance

Cc,Blocked,07/02/2021,Product

Dd,Pending,07/02/2021,Documents

Ee, Blocked,17/02/2021, Finance

Ff, Passed,10/02/2021, Product

Gg,Pending,17/02/2021, Product

Hh, Failed,12/02/2021, Product

Ii,Pending,15/02/2021,Finance

Jj, Blocked,16/02/2021,Documents

Kk,Passed,17/02/2021,Documents

Ll,Passed,16/02/2021,product

Mm,Failed,17/02/2021,Product

In the above data set rangesum(above(count([Test case]),0,rowno()))).  for 16th feb count will be 7 bcz 4 test cases were changed status and executed on 17th efb.

so here I want to store the Actual execution on 16th feb even the status changes  when I refresh on 17th feb

I need to calculate actual execution on 17ht feb i.e. 17th feb minus 16th actual execution

10 cases-9cases =1 case was the required one.

So here I want to store 9 cases for 16th feb even when I refresh on next day,same way need to store 10 cases for the 17th feb even when I refresh on next day same way up to date.

How can I store the 16th data even when data refreshed on 17th Feb and same way cases executed on 17th is 10 so how can I store it when I refresh data on 18th Feb?

[Qlik-Developer]

Hi @stevedark 

I modified above formula to

if(len(tmpSprint ) > 0, left(tmpSprint, 1), len(trim(tmpSprint))-1, '') as Sprint

but showing expression error like if take only 2-3 parameters

then I changed to 

if(len(tmpSprint ) > 0, left(tmpSprint, 1), (len(trim(tmpSprint))-1, '')) as Sprint not working

can you pls provide the correct expression?

Fld1 &if(Fld1 <> '' and Fld2 <> '', ',', '') &
Fld2 &if(Fld1 & Fld2 <> '' and Fld3 <> '', ',', '') &
Fld3 &if(Fld1 & Fld2 & Fld3 <> '' and Fld4 <> '', ',', '')&
Fld4 &if(Fld1 & Fld2 & Fld3 & Fld4 <> '' and Fld5 <> '', ',', '')&
Fld5 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 <> '' and Fld6 <> '', ',', '')&
Fld6 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 <> '' and Fld7 <> '', ',', '')&
Fld7 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 & Fld7 <> '' and Fld8 <> '', ',', '')&
Fld8 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 & Fld7 & Fld8 <> '' and Fld9 <> '', ',', '')&
Fld9 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 & Fld7 & Fld8 & Fld9 <> '' and Fld10 <> '', ',', '')&
Fld10 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 & Fld7 & Fld8 &Fld9 & Fld10 <> '' and Fld11 <> '', ',', '')&
 Fld11 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 & Fld7 & Fld8 &Fld9&Fld10 & Fld11 <> '' and Fld12 <> '', ',', '')&
 Fld12 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 & Fld7 & Fld8 &Fld9&Fld10&Fld11 & Fld12 <> '' and Fld13 <> '', ',', '')&
 Fld13 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 & Fld7 & Fld8 &Fld9&Fld10&Fld11& Fld12 & Fld13 <> '' and Fld14 <> '', ',', '')&
 Fld14 &if(Fld1 & Fld2 & Fld3 & Fld4 & Fld5 & Fld6 & Fld7 & Fld8 &Fld9&Fld10&Fld11& Fld12 & Fld13& Fld14 <> '' and Fld15 <> '', ',', '')& Fld15
as Sprint

tried the above one as well but extra commas are coming, please correct me if I did wrong?

[stevedark]

Hi @Qlik-Developer 

Sorry, I had the syntax wrong above, it should have read:

if(len(tmpSprint ) > 0, left(tmpSprint, len(trim(tmpSprint))-1, '') as Sprint

Without the ,1 (that would only be required for a mid statement).

The code to build the string depends on whether you have empty strings or nulls in there, if it is is nulls it will need to read:

if(not IsNull(Fld1), Fld1, '') &
if(not IsNull(Fld2), Fld2 & if((not IsNull(Fld1)), ',', '')) &
if(not IsNull(Fld3), Fld3 & if((not IsNull(Fld1)) or (not IsNull(Fld2)), ',', '')) &

If you are on the latest version of Sense you can use the Coalesce function, which will make the code a bit less long.

It seems like you could do with getting the assistance of a Qlik consultant in for a few days, they will be able to help you get this done in short order, where it seems that you have been battling this for some time.

Hope you get it sorted soon.

Steve

[stevedark]

Hi @Qlik-Developer 

You may find some pointers for your storing historical data in this post:
https://www.quickintelligence.co.uk/qlikview-incremental-load/

If you just want the count, rather than archiving everything at a point in time, you will need to use a GROUP BY and a COUNT statement in the load.

Hope that helps.

Steve

[Qlik-Developer]

Hi @chrisbrain ,

Thanks for your reply, 

How can I get history and worklog for all the keys at a time.

i can able to pass only one key, I tried different ways   to get all the keys worklog i.e issue transition status and date 

tried like YSP*, ~YSP*,key in(YSP-10,YSP-15) but didn't able to run throwing error.

please suggest to me, how to get all the keys worklog at a time to know the status changing of keys and date of changing.

[Qlik-Developer]

Hi @stevedark ,

i am having sprints field contain values like

Table:

sprints,Key

sprint0,sprint1,10

sprint2,sprint1,11

sprint4,12

sprint3,sprint0,sprint2,13

sprint5,sprint6,14

                               ,15

                                ,16

sprint5,sprint4,17

i want just sprint number from sprints field so i did

keepchar(SubField(Sprints,','),0123456789) as SprintNumber

then after I find:

max(SprintNumber) as maxnumber,

min(SprintNumber) as minnumber,

then after I did :

if(minnumber=SprintNumber,1,0) as sameminsprintflag,
if(maxnumber=SprintNumber,1,0) as samemaxsprintflag 

i used  sameminsprintflag and samemaxsprintflag filter in front end.

but here the issue is the keys (15,16) which are not having sprints is not counting 

so how can I include blank values in the keep char function?