JIRA JQL Help Values exist in one or more

Junaid Shah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 15, 2015

Hi,

 

I need some assistance writing a query.

I have two fields, Country, and, Supplier.

I need a JQL query which I can use on the dashboard to show how many countries each supplier is in.

Each record is 1 country and 1 supplier.

Issue is also that 1 country can have the same supplier multiple times..

For example if Supplier A supplies to UK, USA, Canada

Supplier B supplies to France

etc.

 

Greatly Appreciated, thank you.

2 answers

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

1 vote
Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 7, 2015

Try SQL for JIRA:

select cf.country as "Country", count(*) as "Suppliers"
from issuecustomfields cf inner join issues i
where cf.issueid=i.id where i.JQL="..."  
group by cf.supplier

NOTE: In the SQL query above you have to provide a JQL function to filter the issues like (project in XXXX, YYYY). Otherwise it will not work because full scans on the issue table are not allowed.

 

Then use the complementary SQL for JIRA Reports and Gadgets plug-in to create (in minutes) a nice visualization (charts, tables, etc) for the SQL query.

 

 

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 7, 2015

note please that your custom fields: "country" and "supplier" are columns of the ISSUECUSOMFIELDS table. This is a nice feature of the "SQL for JIRA" plug-in... :)

1 vote
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 15, 2015

JQL is for finding issues, not for processing data.

But, I think there's an easy way to get numbers out - write a filter for "all the issues I am interested in".  Use it in a "2 dimensional filter" gadget, selecting country as one axis and supplier as the other.

You'll get a grid telling you where each supplier is active in a country, and on how many issues (I think that's the goal here?)

Junaid Shah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 15, 2015

Hi, yes I tried that, the issue was with that, is that the Supplier field is not a select list. It is our own type of field which is based on a text field.

Junaid Shah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 15, 2015

This means that the Supplier field does not show and does not allow to be selected in a 2 dimensional statistic filter statistics

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 15, 2015

Ah, it's not "statable" then. I think you're going to struggle with this, as text isn't really that countable.

Junaid Shah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 15, 2015

Do you know of a query which can sort of remove duplicates ? e.g. how you have in SQL DISTINCT?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 15, 2015

Nope, JQL *just* finds issues.

Junaid Shah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 15, 2015

By this I mean So that I can remove duplicate supplier records with the same country value, so that 1 supplier per 1 country shows.

Junaid Shah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 15, 2015

Okay, thanks anyway though! :)

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 15, 2015

Sounds like a good housekeeping thing to do :-) There are some options, but I'm not sure how much they'd help. You can run JQL like "Supplier ~ Acme" and JIRA will try to catch some variations on the word Acme in your suppliers field. I've usually found this useful for housekeeping because it will get a good chunk of the words I'd like to de-duplicate. Won't catch them all, but it's better than reading them one at a time!

Junaid Shah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 15, 2015

Yeah housekeeping is always fun isnt it... lol. Unfortunately even stuff like ~ would not work.. Because Supplier names will all be the same.. So regardless they'd all show. But anyway I guess this is an adventure to figure out what to do! Thanks for your help :)

TAGS
AUG Leaders

Atlassian Community Events