I have 3 tables that hold information for Projects, Milestones and Tags.
If a milestone is related to a specific business milestone this is tagged with that milestone name. The names of these business milestones are the same across all projects but the actual date will depend on the project.
Due to the structure of the application we are using milestones cannot have dependencies on other milestones and cannot be rolled up.
I need to be able to use the Tag to 'look up' the relevant date e.g. is the milestone has the tag 'BUSMS 1' then it needs to return the Milestone date of the milestone called 'BUSMS 1' as demonstrated in the table below. The Eng Milestones will become part of the bigger Business Milestone
Essentially I'm wanting to do the following:
if Milestone tag name='BUSMS 1' then return the MilestoneDate where Milestone Name = 'BUSMS 1'
I've tried apply map but it feels like it might be a daisy chain of apply maps? Does it need a separate table? A temp table? Slowly going crazy here!
Project Table fields: Project ID, Project Name
Milestone Table fields : Milestone ID, Milestone Name, Project ID, MilestoneLink MilestoneDate
Milestone Tags come from a file with just the Unique ID numbers in which is then used to pull the name from another file. Script as follows:
MilestoneTagIds:
LOAD
MilestoneTagIds ,
Link as MilestoneLink
FROM [MilestoneTagIds.qvd]
(qvd);
left join
LOAD
TagId as MilestoneTagIds,
TagName as MilestoneTagName,
FROM [Tags.qvd]
(qvd);
| Project |
Milestone Name |
Date |
Tags |
BusMS Date |
| ABC |
Eng Milestones1 |
04/03/2024 |
BusMS 1 |
30/06/2024 |
| ABC |
Eng Milestones2 |
12/09/2024 |
BusMS 1 |
30/06/2024 |
| ABC |
Eng Milestones3 |
30/01/2025 |
|
|
| ABC |
Eng Milestones4 |
|
|
|
| ABC |
Eng Milestones5 |
12/10/2024 |
BusMS 2 |
12/07/2024 |
| ABC |
Eng Milestones6 |
09/07/2024 |
BusMS 2 |
12/07/2024 |
| ABC |
Eng Milestones7 |
|
|
|
| ABC |
BusMS 2 |
12/07/2024 |
|
|
| ABC |
BusMS 1 |
30/06/2024 |
|
|
| DEF |
Eng Milestones1 |
04/03/2024 |
BusMS 1 |
30/09/2024 |
| DEF |
Eng Milestones2 |
12/09/2024 |
BusMS 1 |
30/09/2024 |
| DEF |
Eng Milestones3 |
30/01/2025 |
|
|
| DEF |
Eng Milestones4 |
|
|
|
| DEF |
Eng Milestones5 |
|
BusMS 2 |
12/04/2025 |
| DEF |
Eng Milestones6 |
|
BusMS 2 |
12/04/2025 |
| DEF |
Eng Milestones7 |
|
|
|
| DEF |
BusMS 2 |
12/04/2025 |
|
|
| DEF |
BusMS 1 |
30/09/2024 |
|
|
