Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
you can add a new field call it LastStage
StageDates:
load * inline [
StageStart,StageEnd,ProjectName,StageName
1/1/2020,3/31/2020,Project 1,Stage 1
4/1/2020,6/30/2020,Project 1,Stage 2
7/31/2020,12/31/9999,Project 1,Stage 3
1/1/2020,2/28/2020,Project 2,Stage 1
3/1/2020,6/30/2020,Project 2,Stage 2
7/31/2020,12/31/2020,Project 2,Stage 3
1/1/2020,4/31/2020,Project 3,Stage 1
5/1/2020,6/30/2020,Project 3,Stage 2
7/31/2020,12/31/2020,Project 3,Stage 3
];
NoConcatenate
costmonth:
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
];
inner join
intervalMatch(costmonth, ProjectName)
load StageStart, StageEnd, ProjectName
resident StageDates;
inner join (costmonth)
load StageStart,StageEnd,ProjectName,StageName Resident StageDates;
drop table StageDates;
inner join (costmonth)
load ProjectName, MaxString(StageName) as LastStage
Resident costmonth
group by ProjectName