Table Transformer - Multiple conditions one calculated field

[Emil]

Hello,

I would like to run multiple checks on issues by using multiple conditions (CASE-WHEN-THEN) in Table Transformer macro while using the same calculated field to present the results of these checks as a summary. And I would like to know how to combine multiple conditions with the same calculated field.

I would like something like this (if it worked):

CASE
WHEN FLOOR(DATEDIFF(DAY,'Created',"today")) > 16 THEN "Aging issue"

WHEN FLOOR(DATEDIFF(DAY,'Updated',"today")) > 14 THEN .+ "\nUpdated > 10 days" 
END AS 'Checks',

Thanks a lot,

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Emil ,

I can suggest using the following query structure for your case:

SELECT 'Issue', 'Created', 'Updated',
FORMATWIKI('Checks Created' + " \n" + 'Checks Updated') AS 'Checks'
FROM (SELECT *,
CASE
WHEN 'Checks Created' IS NULL
THEN "-"
ELSE 'Checks Created'
END AS 'Checks Created',
CASE
WHEN 'Checks Updated' IS NULL
THEN "-"
ELSE 'Checks Updated'
END AS 'Checks Updated'
FROM (SELECT *,
CASE
WHEN DATEDIFF(DAY,'Created',"today") > 16
THEN "Aging issue"
END AS 'Checks Created',
CASE
WHEN DATEDIFF(DAY,'Updated',"today") > 14
THEN "Not updated issue"
END AS 'Checks Updated'
FROM T*))

As you can see, here we have the three levels of the SELECT function.

The first one that is internal is used to run different columns through different conditions and the result is written into separate columns as well ('Checks Created' and 'Checks Updated' for my example).

The second SELECT is used to replace empty cells with some character ("-" for my example). If your conditions on the previous step are built in a way that there will be no empty cells, you may miss this step.

The third external SELECT is used to combine all your result columns into one master column ('Checks' for my example).

Hope this helps or at least gives you some further ideas how to transform your tables.

[Emil]

Hello @Katerina Kovriga {Stiltsoft} ,

Thank you very much. I really-really appreciate your answer and the fact that you have replied so quickly. The code is working very well and it will help a lot. :-)

I've just made slight modifications and try to understand the code in the meantime.

First modification was that I've moved the "\n" and the "-"s to

THEN "- Aging issue"

and 

THEN "\n - Not updated issue". 

This way I guess there is only line break and bullet points before the lines in Checks field if the Checks Created and Checks Updated fields are not NULL.

And the second modification was that I've replaced the "-" with "" when 'Checks Created' IS NULL or 'Checks Updated' IS NULL. This way no bullet points appeared in Checks field when these fields were NULL.

WHEN 'Checks Created' IS NULL
THEN ""

and 

WHEN 'Checks Updated' IS NULL
THEN ""

So, finally this was my code with the slight modifications:

SELECT 'Issue', 'Created', 'Updated',
FORMATWIKI('Checks Created' + 'Checks Updated') AS 'Checks'
FROM (SELECT *,
CASE
WHEN 'Checks Created' IS NULL
THEN ""
ELSE 'Checks Created'
END AS 'Checks Created',
CASE
WHEN 'Checks Updated' IS NULL
THEN ""
ELSE 'Checks Updated'
END AS 'Checks Updated'
FROM (SELECT *,
CASE
WHEN DATEDIFF(DAY,'Created',"today") > 16
THEN "- Aging issue"
END AS 'Checks Created',
CASE
WHEN DATEDIFF(DAY,'Updated',"today") > 14
THEN "\n- Not updated issue"
END AS 'Checks Updated'
FROM T*))

Anyway, it is working very well. That was exactly what I was looking for. So, thank you a 1000 times again. :-)

Emil 

[Emil]

Hi,

One more question to this topic. I tried to set a fixed column width to the calculated 'Check' field but it didn't work. Actually the column width has been set to 200px but the texts {cell:width=200px} and {cell} also appeared in the field in each case.

I've tried it this way:
FORMATWIKI("{cell:width=200px}" + 'Checks Created' + 'Checks Updated' + "{cell}") AS 'Checks',

This way:
FORMATWIKI("{cell:width=200px}" + ('Checks Created' + 'Checks Updated') + "{cell}") AS 'Checks',

And this way:
FORMATWIKI("\"{cell:width=200px}\"" + ('Checks Created' + 'Checks Updated') + "\"{cell}\"") AS 'Checks',

The interesting thing that with another simple column it worked well and then the texts {cell:width=200px} and {cell} didn't appear:

FORMATWIKI("{cell:width=200px}" + 'Last Comment'+ "{cell}") AS 'Last Comment',

Any idea?

[Katerina Kovriga {Stiltsoft}]

Use an additional Table Transformer - wrap it outside the first one. 

In this case all the transformations and calculations done in the internal Table Transformer macro won't be visible for the external Table Transformer macro. The external macro will work with your table as a plain one and the FORMATWIKI option won't be overloaded and give inconsistent results.

[Emil]

Hello Katerina,

Sorry for the late answer, I had the chance just now to try out what you wrote last time. Thanks a lot, it is working. I've used an additional Table Transformer and in the external one I could set the fixed column width with FORMATWIKI. The only thing that in Chrome the result of the different checks that were bullet points before now appears in the same line without any line breaks in the Checks field although the line breaks are still there in the query. Because actually in the picture below we should see this in the Checks field:

• Long-running issue
• Is the Assignee OK?

Chrome:

And in Firefox only the result of the second check appears, the first check doesn't for the same row in the table:

Do you have any idea?

Emil

[Katerina Kovriga {Stiltsoft}]

Hi @Emil ,

Please check this part of our documentation - the "Preserving initial cell formatting" section.

Seems that it may help (the difference is in pluses and commas for the same FORMATWIKI query).

[Emil]

Thanks a lot again. It is working this way in Chrome. Although in Firefox still only the result of the second check appears but we can use Chrome.

[Katerina Kovriga {Stiltsoft}]

It's a strange behaviour for Firefox, we try to support it.

Please check if you have its latest version installed, then check if the app's version is up-to-date (the current one is 8.6.1).

If the issue persists, please create a support request.

Attach the page storage format (upper right corner, menu ..., view storage format). If you don't see the option, ask your administrator to do it for you. Then we'll be able to recreate exactly your macros and queries.

As your data comes from Jira, it won't be recreated, so please take a screenshot of the Jira Issues macro (when the page is published and the macro is not wrapped into other macros). The headers should be visible.

We'll try to look closer into the issue.

[Emil]

Thank you very much. I will check the versions first.

[Emil]

Ooooh, I've changed the language of Firefox to English (US), and now it is working properly too.

[Katerina Kovriga {Stiltsoft}]

Oh, yes - the Jira fields might be translated differently for different languages, didn't think about it at the moment.

Glad you solved the issue!