Moving a field from one table to another based on dates

[RogerG]

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?

[edwin · Accepted solution]

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;

[MayilVahanan]

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:

[RogerG]

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

[RogerG]

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

[edwin]

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)

[RogerG]

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.

[edwin]

ok