Position costs in chart

RogerG — Mon, 18 Jan 2021 23:53:18 GMT

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?

Re: Position costs in chart

edwin — Tue, 19 Jan 2021 18:45:55 GMT

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

topic Re: Position costs in chart in QlikView

Position costs in chart

Re: Position costs in chart