Combine two table using table transformer and filter showing the next Milestone

Deborah Frommer August 18, 2023

Hi all, 

I have two tables:

- One table is a list of ongoing project with Project Manager and Project Status
Projects.png

- The other is a table with project milestones of the projectsMilestones.png

As a result I would like to have a list with the projects and next Milestone

Results.png

The difficulty is, that two Milestones can be on the same day and then I have to merge the cell. Thats I tried with CONCAT or with Group

The other issue I have is, that I can filter the older Milestones (older than today), but how can I only filter the next Milestone?

 

Many thanks for your help

1 answer

1 accepted

3 votes
Answer accepted
Stiltsoft support
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.
August 19, 2023

Hi @Deborah Frommer ,

Hope the following solution will help your case:

Sun 1-1.png

Here is the SQL query for the internal Table Transformer macro:

SELECT *,
MIN('Days')
FROM
(SELECT *,
DATEDIFF(day, "today", 'Milestone-Date') AS 'Days'
FROM
(SELECT 'Project', 'Milestone', 'Milestone-Date' FROM
(SELECT T1.'Project',
SUM(T1.'Milestone' + " ") AS 'Milestone',
T1.'Milestone-Date'
FROM T*
GROUP BY 'Project', 'Milestone-Date')
WHERE 'Milestone-Date' > "today"))
GROUP BY 'Project'

Sun 1-2.png

What we do here:

SELECT T1.'Project',
SUM(T1.'Milestone' + " ") AS 'Milestone',
T1.'Milestone-Date'
FROM T*
GROUP BY 'Project', 'Milestone-Date' - we group milestones that belong to the same project and have the same date

SELECT 'Project', 'Milestone', 'Milestone-Date' FROM
...
WHERE 'Milestone-Date' > "today" - we filter out milestones that have already passed

SELECT *,
DATEDIFF(day, "today", 'Milestone-Date') AS 'Days'
FROM ... - we count days difference between "today" and the Milestone-Date. Please make sure that in the Settings tab of your Table Transformer macro you have the correct date format. For our example it will be d M yy.

SELECT *,
MIN('Days')
FROM
...
GROUP BY 'Project' - here we select the next milestone (the minimum days difference).

And here is the SQL query for the external Table Transformer macro:

SELECT T1.'Project', 'PM', 'Status',
'Milestone' AS 'Next Milestone',
'Milestone-Date'
FROM T1 OUTER JOIN T2 ON T1.'Project' = T2.'Project'

Sun 1-3.png

Here we simply use the standard Look up preset but list the required columns that we want to display.

By the way, you may try to visualize your data with the help of our advanced Gantt Chart. It allows you to show milestones and events as well. 

Deborah Frommer August 20, 2023

That's an great solution.

Many thanks for your quick help!

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events