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

[Deborah Frommer]

Hi all, 

I have two tables:

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

- The other is a table with project milestones of the projects

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

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

[Stiltsoft support · Accepted solution]

Hi @Deborah Frommer ,

Hope the following solution will help your case:

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'

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'

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]

That's an great solution.

Many thanks for your quick help!