Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
Lets say I have a table with projects and phases along with costs
Phases:
load * inline [
Project, phase, startdate, enddate
P1, p1, 1/1/2020, 3/30/2020
p1, p2, 4/1/2020, 5/1/2020
]
costs
load * inline [
activity, account, CostDate, Cost
a1,Act1, 1/4/2020, 1000
a1,Act2, 2/4/2020, 3000
a1,Act3, 4/15/2020, 2000
]
I can create the master table of to get to this, using joins, and interval match, but it duplicates the costs across the phases. (change Cost to Phase)
but what I need is (change Cost to Phase) How do I keep the other phases null?
try this
Phases:
load * inline [
Project, phase, startdate, enddate
P1, p1, 1/1/2020, 3/30/2020
P1, p2, 4/1/2020, 5/1/2020
];
inner join (Phases)
load * inline [
activity, account, CostDate, Cost
a1,Act1, 1/4/2020, 1000
a1,Act2, 2/4/2020, 3000
a1,Act3, 4/15/2020, 2000
];
NoConcatenate
NewPhase:
load
Project, phase, startdate, enddate,
activity, account, CostDate, if(CostDate>=startdate and CostDate<=enddate,Cost) as Cost
Resident Phases;
drop table Phases;
try this
Phases:
load * inline [
Project, phase, startdate, enddate
P1, p1, 1/1/2020, 3/30/2020
P1, p2, 4/1/2020, 5/1/2020
];
inner join (Phases)
load * inline [
activity, account, CostDate, Cost
a1,Act1, 1/4/2020, 1000
a1,Act2, 2/4/2020, 3000
a1,Act3, 4/15/2020, 2000
];
NoConcatenate
NewPhase:
load
Project, phase, startdate, enddate,
activity, account, CostDate, if(CostDate>=startdate and CostDate<=enddate,Cost) as Cost
Resident Phases;
drop table Phases;
Hi @RogerG
Try like below
Phases:
load * inline [
Project, phase, startdate, enddate
P1, p1, 1/1/2020, 3/30/2020
p1, p2, 4/1/2020, 5/1/2020
];
costs:
load * inline [
activity, account, CostDate, Cost
a1,Act1, 1/4/2020, 1000
a1,Act2, 2/4/2020, 3000
a1,Act3, 4/15/2020, 2000
];
IntervalMatch(CostDate)
Load startdate, enddate
Resident Phases;
Join
LOAD * Resident Phases;
Join
LOAD * Resident costs;
DROP Table Phases, costs;
o/p:
This solution worked, but I am trying to take the next step in my path,
I need to be able to view all the projects with all the phases, and costs assigned to them. but when i try to join them together, i end up with a full sum of all costs, or end up with the costs replicated across all account/phase combinations.
any help would be appreciated
Mayiln, your solution seems to remove the 0 elements, I need to keep a cross table of all possible values, weather they have costs or not
not sure what the issue is. what i do observe is Account is not specific to a project so it is showing up for all projects where the cost dates will fall within the phases for that project - this is why you are seeing the costs duplicated (if that is the issue)
That is the issue, I needed the costs to align with the account that entered it. not all of them. the accepted solution worked for me.
ok