So I have costs in a chart that I am trying to control the location
I currently have it working where when the costs are in the rage of the stage, they appear in the proper stage and year
Projects:
load * inline [
ProjectName, Phase
Project1, 1
Project2, 2
Project 3, 4
]
StageDates:
load inline * [
ProjectName, StageName, StageStart, StageEnd
project 1, Stage 1, 1/1/2020, 3/31/2020
Project 1, Stage 2, 4/1/2020, 6/30/2020
Project 1, Stage 3, 7/31/2020, 12/31/9999
project 2, Stage 1, 1/1/2020,2/31/2020
Project 2, Stage 2, 3/1/2020, 6/30/2020
Project 2,Stage 3, 7/31/2020, 12/31/2020
project 3, Stage 1, 1/1/2020, 4/31/2020
Project 3, Stage 2, 5/1/2020, 6/30/2020
Project 3, Stage 3, 7/31/2020, 12/31/2020
];
then I have the costs table generated through a intervalMatch function
load * inline[
ProjectName, costid, costtype, costmonth
Project 1, 1111, External, 1/1/2020
Project 1, 1112, External, 2/1/2020
Project 1, 1113, Internal, 3/1/2020
Project 1, 1114, Internal, 4/1/2020
Project 2, 2222, External, 1/1/2020
Project 2, 2223, External, 2/1/2020
Project 2, 2224, Internal, 3/1/2020
Project 2, 2225, Internal, 4/1/2020
Project 3, 3333, External, 1/1/2020
Project 3, 3343, External, 2/1/2020
Project 3, 3365, Internal, 3/1/2020
Project 3, 3464, Internal, 4/1/2020
]
left join
intervalMatch(costmonth)
load StageStart, StageEnd
resident StageDates;
left join
load * resident StageDates;
now what will happen above is the dates will be spread through Stage1, 2 and 3
however what I want to do is be able to look at the project and say if the Project is Phase 4, then all costs go to Stage 3 regardless of dates.
I still need to display all the stage dates in a different table as well.
any thoughts on how to override this for the interval match overall?
