Multiple levels from one level

[amanjain57]

Hello

I have a case where a project has hours accounted for it. 

Example

Project	Hours
ABC	1000

I want to distibute these hours by different phases by different percentages like below.  So the 1000 hours will be allocated based on these percentage to different phases. 

Phase	% allocation
PR1	16.05%
PR2	7.80%
PR3	10.13%
PR4	11.14%
PR5	10.11%
PR6	25.38%
PR7	10.88%
PR8	8.32%

Can some one help to understand how can i model this in Qlik?

Thanks

Aman

[Vegar · Accepted solution]

If you have more than one project you will need to identify the project per phase row. See attached qvf

[dplr-rn]

2 options

1- link your project table with the phase one similar to below and do calculation on the front end

load * inline [
Project,Hours
ABC,1000
];
load * inline [
Project,Phase,% allocation
ABC,PR1,	16.05%
ABC,PR2,	7.80%
ABC,PR3,	10.13%
ABC,PR4,	11.14%
ABC,PR5,	10.11%
ABC,PR6,	25.38%
ABC,PR7,	10.88%
ABC,PR8,	8.32%
];

2 - join the 2 tables. then do another resident load to split the values based on the 2 columns

e.g.

ProjectTemp:
load * inline [
Project,Hours
ABC,1000
];
Outer join (ProjectTemp)
load * inline [
Project,Phase,% allocation
ABC,PR1,	16.05%
ABC,PR2,	7.80%
ABC,PR3,	10.13%
ABC,PR4,	11.14%
ABC,PR5,	10.11%
ABC,PR6,	25.38%
ABC,PR7,	10.88%
ABC,PR8,	8.32%
];

Project:
load *,
	[% allocation] * Hours as PhaseHours
resident ProjectTemp;
drop table ProjectTemp;

[dplr-rn]

#1 is what vegar is suggesting

[Channa]

you need to add projects to Phases table you can't join with out it