Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
orangebloss
Creator

Returning Date from another field in another table

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    
Labels (5)
1 Reply
G3S
Creator III

does swapping the join work? loading tags first and joining milestones