Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Yes, DISTINCT is not allowed in the load script, and all CONCAT statements are inherently distinct. That was an error on my part.
Glad you arrived at where you wanted to here.
Thank you working fine,
i am having "bugcreated" date field i want to show open defects aging on xaxis.
for that i have created like this
if(bugcreated =floor(Today()),'0',if(bugcreated=floor(today()-1),'1',if(bugcreated=floor(today()-2),'2',
if(bugcreated=floor(today()-3),'3',if(bugcreated=floor(today()-4),'4',if(bugcreated=floor(today()-5),'5',
if(bugcreated=floor(today()-6),'6',if(bugcreated=floor(today()-7),'7',if(bugcreated=floor(today()-8),'8',
if(bugcreated=floor(today()-9),'9',if(bugcreated>=floor(today()-30),'10-30',
if(bugcreated<=floor(today()-30),'>30')))))))))))) as BugDays
here i want to exclude weekends how can i do that?
when i load file monday sholud show as age 0,friday as age 1.instead of sunday as day1 and saturday as day2.
To ignore weekends you want to use the NetworkDays function, something like:
if(networkdays(bugcreated,today()) = 0, dual('0', 0),
if(networkdays(bugcreated,today()) <=1, dual('1', 1),
if(networkdays(bugcreated,today()) <=5, dual('2-5', 5),
etc.
The parameters of network days may need to go the other way around to get positive numbers, and you may need to add or subtract a day to get the value you want.
The dual function is important here, as having the buckets in strings as you have there the values will not sort correctly, the dual function allows things to sort correctly.
Would I be right in guessing your list currently goes:
>30
1
10-30
2
3
etc.?
Steve
if(networkdays(bugcreated,today()) = 0, dual('0', 0),
if(networkdays(bugcreated,today()-1) <=1, dual('1', 1),
if(networkdays(bugcreated,today()-2) <=2,dual('2', 2),
if(networkdays(bugcreated,today()-3) <=3,dual('3', 3),if(networkdays(bugcreated,today()-4) <=4,dual('4', 4),
if(networkdays(bugcreated,today()-30) >=30 dual('10-30', 5),
if(networkdays(bugcreated,today()-30) <=30 dual('>30', 6))))))))) as bugageing
Is this correct ? Please help if i m wrong
Hi steve thanks for your reply.
In some projects issues were logged on saturday as well.when we load on monday it should consider saturday as working day so monday is 0 aging, saturday is 1day ageing , friday is 2day ageing.
So how can we include saturday as working day from networking days?
Thanks in advance!!!!!!
You don't need to be doing the minus numbers, NetworkDays takes care of that. The less than and greater than on the 30 seem to be the wrong way around. Also you don't actually need to have the greater than, as you can have the final Dual as the False part of the last IF.
Hopefully that will get you where you need to get to.
Steve
Not sure from the top of my head how network days works when one or other of the days falls on a weekend. You may need to adjust the figure by adding 1, based on the WeekDay function.
if(networkdays(createddateref,today()) = 0, dual('0', 0),
if(networkdays(createddateref,today()) <=1, dual('1', 1),
if(networkdays(createddateref,today()) <=2,dual('2', 2),
if(networkdays(createddateref,today()) <=3,dual('3', 3),if(networkdays(createddateref,today()) <=4,dual('4', 4),
if(networkdays(createddateref,today()) <=5,dual('5', 5),if(networkdays(createddateref,today()) <=6,dual('6', 6),
if(networkdays(createddateref,today()) <=7,dual('7', 7),if(networkdays(createddateref,today()) <=8,dual('8', 8),
if(networkdays(createddateref,today()) >=8 ,dual('8+', 9)))))))))))as bugageing
in load editor i have created the bugageing field.
when createddateref = today() it should return 0 ageing, but here for 05-nov-2020 coming 1day ageing ,how to retrieve the 0 day?
If it's counting the first day as 1 and you want to count it as zero you can take 1 off each time you use the function.
if(networkdays(createddateref,today())-1 = 0, dual('0', 0),
if(networkdays(createddateref,today())-1 <=1, dual('1', 1),
if(networkdays(createddateref,today())-1 <=2,dual('2', 2),
if(networkdays(createddateref,today())-1 <=3,dual('3', 3),
if(networkdays(createddateref,today())-1 <=4,dual('4', 4),
if(networkdays(createddateref,today())-1 <=5,dual('5', 5),
if(networkdays(createddateref,today())-1 <=6,dual('6', 6),
if(networkdays(createddateref,today())-1 <=7,dual('7', 7),
if(networkdays(createddateref,today())-1 <=8,dual('8', 8),
if(networkdays(createddateref,today())-1 >=8 ,dual('8+', 9))))))))))) as bugageing
You could simplify this by using a preceding load, and working out the days at one level and then bucket it on another.
With the bucketing as it is you could simplify to:
if(networkdays(createddateref,today())-1 <=8,
dual(networkdays(createddateref,today())-1,
networkdays(createddateref,today())-1)
, dual('8+', 9)) as bugageing
I have created BugDays dimension:
if(bugcreated >=floor(Today()-5),'0-5',if(bugcreated>=floor(today()-10),'5-10',if(bugcreated>=floor(today()-20),'10-20',if(bugcreated<=floor(today()-20),'>20')))) as BugDays;
Uesd Bugdays and priority as dimensions.
How can i dispaly all the 4 legneds values on x-axis,even if value is 0.In above image for 0-5 days bucket showing only one legend value,5-10 days showing only 3 legend values,for 10-20 and >20 showing 4 legends.
How to show all the four legned values for each bukect of days even if value is 0 like 10-20 and >20 bucket days