Qlikview and jira custom fields - please help

[#1]

Hi all

Newbie(ish) here, we're reviewing Qlikview to see if it fits our needs, and I've been tasked (because I have some passing experience with Qlikview) with taking our manual reports from our on prem JIRA (which were done by way of excel export) and recreating them through Qlikview directly from our slave database.

I have it all connected up, that was mostly nice and easy, as were the first few charts, until I got to ones that use JIRA's custom fields. I have heard / read (through google-fu) that Custom Fields were tricky.

I concur, they are.

for example

I have a custom field "cost centre"  which contains a list of all cost centres across the business, each jiea ticket has one option selected from the cost centre, however when I do a pie chart on ticket count vs cost centre the pie chart (or any other chart for that matter) attributes the total number of tickets to each cost centre. Clearly I've made an error somewhere in connecting up the schema (missed a table to something), but I can't see it.

If anyone has done this before, I'm looking for help.

All help gratefully received.

Thanks

Rob

[#2 · Accepted solution]

Each custom field needs its' own entry in the script

ODBC CONNECT TO [JIRA-server];

LOAD

     [fieldnames],

     custom1,

     custom2

SQL SELECT

     iss.[fieldnames],

     cfo_custom1.customvalue as "custom1",

     cfo_custom2.customvalue as "custom2"

FROM

     jira.public.jiraissueas iss

LEFT JOIN

  public.customfieldvalue AS cfv_custom1 ON (iss.id = cfv_custom1.issue AND cfv_custom1.customfield = xxxxx)

LEFT JOIN

  public.customfieldoption AS cfo_custom1 ON (cast(cfv_custom1.stringvalue AS integer) = cfo_custom1.id)

LEFT JOIN

  public.customfieldvalue AS cfv_custom2 ON (iss.id = cfv_custom2.issue AND cfv_custom2.customfield = yyyyy)

LEFT JOIN

  public.customfieldoption AS cfo_custom2 ON (cast(cfv_custom2.stringvalue AS integer) = cfo_custom2.id)

xxxxx and yyyyy are the id numbers of the specific custom fields in JIRA.

[jmalenfant]

Is there an easier way than to manually change the script, that is prone to error and not scalable as new custom fields are added?

We're trying to use 2 API calls with the Qlik Sense rest connector:

1. a call to a specific JIRA filter

2. a call to get the custom fields and names - https://ourprojectname.atlassian.net/rest/api/latest/field/‌

But when we load the data from both calls into an app, running into issues where we have no idea how to just create a simple table with JIRA issues and columns that display the actual name for a custom field, not the ID.

Any thoughts on this? I'm guessing this is a common issue.
Thanks!!