Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
RogerG
Creator
Creator

Position costs in chart

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?

1 Reply
edwin
Master
Master

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