topic Re: Moving a field from one table to another based on dates in QlikView

Moving a field from one table to another based on dates

RogerG — Tue, 26 Jan 2021 20:19:24 GMT

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?

Re: Moving a field from one table to another based on dates

edwin — Tue, 26 Jan 2021 21:58:41 GMT

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;

Re: Moving a field from one table to another based on dates

MayilVahanan — Wed, 27 Jan 2021 03:31:23 GMT

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:

Re: Moving a field from one table to another based on dates

RogerG — Wed, 27 Jan 2021 07:01:23 GMT

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

Re: Moving a field from one table to another based on dates

RogerG — Wed, 27 Jan 2021 07:03:35 GMT

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

Re: Moving a field from one table to another based on dates

edwin — Wed, 27 Jan 2021 17:05:29 GMT

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)

Re: Moving a field from one table to another based on dates

RogerG — Wed, 27 Jan 2021 17:22:36 GMT

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.

Re: Moving a field from one table to another based on dates

edwin — Wed, 27 Jan 2021 18:07:22 GMT