SQL query to count empty values

[Esteban Borja Lopez]

Hello community, 

Im dealing with a very basic case Im not able to make it work, to count the amount of tickets out from a given JIRA filter that has a specific field empty.

I've tried the following expressions to count the total tickets with field "Time Spent"  empty vs the non-empty ones (irrespective of the value), so I can calculate the percentage out of those 2, but does not work for this case:

• SUM(IF(T4.'Time Spent' is EMPTY 1, 0)) AS 'Total empty'
• SUM(IF(T4.'Time Spent' is not EMPTY 1, 0)) AS 'Total not-empty'
• SUM(IF(T4.'Time Spent' is EMPTY 1, 0))/(SUM(IF(T14'Time Spent' is EMPTY 1, 0))+SUM(IF(T4.'Time Spent' is not EMPTY 1, 0))) AS 'Fulfilment %'

I've tried with with "= EMPTY", "is NULL", "= ''", etc... but get either error or weird results. In the case Im using, for simplicity purposes I have only 1 ticket being dropped by the filter, and the "Time Spent" value s empty, and by using above expression the results I get are

Expected results: 

• Total empty: 1
• Total not-empty: 0
• Fulfilment %: 0

Any hint on what am I doing wrong?

Thanks in advance,

Esteban

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Esteban Borja Lopez ,

I've tried to reproduce the issue for the project from our own Jira instance:

Here I have ten issues and all of them are with the empty Due Date field (we leave this field blank for this project).

SELECT "Empty Total" AS '',
SUM(IF(T1.'Due' IS NULL, 1, 0)) AS 'Empty Due Dates',
SUM(IF(T1.'Due' IS NOT NULL, 1, 0)) AS 'Not Empty Due Dates'
FROM T1

The result seems to be satisfactory.

So please try to use the NULL/NOT NULL functions once more. If nothing helps, please raise a support ticket. The portal is confidential, so please provide us with the full SQL query that you are using and the screenshot of your Jira Issues macro in the page view mode (without any macros and with the visible headers). We'll look into the issue closely.

[Esteban Borja Lopez]

Hi @Katerina Kovriga {Stiltsoft} , thanks for the reply.

I think there is a problem with SQL or the macro app itself as I've realised that same code sometimes works, and stops working suddenly without no changes on the code (for this case and others scenarios).

I've tried IS NULL NOT and IS NULL expressions with same unexpected results, but this morning, when trying it again, it returned the desired values. However, when I extend the SLQ code to fetch/calculate the same but from different tables within the same macro, it gives either error or weird numbers.

Same is happening with other coding for this case:

* Using just 1 block of code for 2 tables, it works:

* But when replicating the code to 4 tables (in same macro), it does not:

Im checking with with IT team to further open a ticket to Atlassian for review.

In addition, as per the code the "NaN%" should be set as "-" but Im having problems to validate it properly when the value (a result of a division) is not a number, like the case above where it's basically doing 0 / 0 = "NaN" (Not a Number)... is there any "IS NOT INTEGER" or similar to validate it? I've tried several ones and still not make it work.

Thanks in advance and Regards,

Esteban

[Katerina Kovriga {Stiltsoft}]

Hi @Esteban Borja Lopez ,

Maybe you should separate the Jira Issues macros and the Table Transformer macros? Leave one source table per macro? The issues may come from the dynamic origin of the source macros (maybe some of the Jira Issues macros are slower to download the information, the Table Transformer macro "sees" the data partly and there is certain inconsistency in the results).

You can also create a support request - our team will look into the issue closely (it's confidential, you'll be able to share your data and queries freely).

What concerns your second question, I can suggest the following workaround to simply replace your "NaN%", "Infinity" and other errors with the CASE WHEN condition:

SELECT 'A', 'B',
CASE WHEN 'C' LIKE "Infinity"
THEN "-"
ELSE 'C'
END
AS 'C'
FROM (SELECT *, 'A'/'B' AS 'C' FROM T*)

[Esteban Borja Lopez]

Hi @Katerina Kovriga {Stiltsoft}

indeed, I was about to test that... I will proceed with splitting the macros to make the internal queries/calculations more agile, the same for the automation rules that some are heavy enough to compromise the performance. Will tell you the results once tested.

On regards the proposed workaround, I couldn't make it work adapting my code, still debugging as seems simple enough to fail. What I've tried instead is LIKE "" and seems to work nicely:

 

Sometimes try and error drops good results :)

[Nic Brough -Adaptavist-]

I suspect trying to use SQL is the problem here - it can't find things that are not there.  "Is empty" has nothing to work with.

[Esteban Borja Lopez]

Hi @Nic Brough -Adaptavist- , thanks for the reply.

Indeed, "is empty" was one of the tries, but the ones working are IS NULL or IS NOT NULL... the problem is in my case it works intermittently as explain above so it make my life a head-ache during debugging the code.

Regards,

Esteban