how to allocate these amounts

[qlikhans]

Hi All,

I have employees allocated to a region (Table 1). I have total expenses per employee per month (Table 2). Needed output is expenses per employee per region.

Table 1:
Staff code	Region	region allocation
NN001	REG A	20%
NN001	REG B	80%
NN002	REG A	100%
NN003	REG B	30%
NN003	REG A	70%
NN004	REG B	25%

Table 2:
Date	Staff code	Amount
31-1-2018	NN001	100
31-1-2018	NN002	30
31-1-2018	NN003	200
31-1-2018	NN004	70
28-2-2018	NN001	20
28-2-2018	NN002	50
28-2-2018	NN003	300
28-2-2018	NN004	150
31-3-2018	NN001	179
31-3-2018	NN002	194
31-3-2018	NN003	208
31-3-2018	NN004	222

The required output in this example would be like below. Total amount per employee is allocated to the region based on the percentage of table 1  

			Region
Date	Staff code	Amount	Reg A	Reg B
31-1-2018	NN001	100	20	80
31-1-2018	NN002	30	30	-
31-1-2018	NN003	200	140	60
31-1-2018	NN004	70	18
28-2-2018	NN001	20	4	16
28-2-2018	NN002	50	50	-
28-2-2018	NN003	300	210	90
28-2-2018	NN004	150	38
31-3-2018	NN001	179	36	143
31-3-2018	NN002	194	194	-
31-3-2018	NN003	208	146	62
31-3-2018	NN004	222	56

could someone explain me how to do this, or even better (if it is a simple excercise) to create a .qvw based on these data?

thank you in advance

brgrds

Hans

[its_anandrjs]

On UI part or in Back end you required to do this.

[its_anandrjs · Accepted solution]

Check the attached App

[its_anandrjs]

On the base of your solution check the attached also but question here is in second table you have field Amount which has no bifurcation of the region for what it is will it distribute equals to region check the  NN003 example

Out Put

[eduardo_dimperio]

Try this:

Table1:

Load

*

FROM

qvdfromtable1

Left Join(Table1)

//Table2:

Load

*

FROM

qvdfromtable2

To understand the concept read this article Understanding Join, Keep and Concatenate

[antoniotiman]

Hi Hans,

may be this

[Table 1]:
LOAD Staff_code, Region, Num(region_allocation) as region_allocation  Inline [
Staff_code, Region, region_allocation
NN001, REG A, 20%
NN001, REG B, 80%
NN002, REG A, 100%
NN003, REG B, 30%
NN003, REG A, 70%
NN004, REG B, 25%
];
[Table 2]:
Join
LOAD * Inline [
Date Staff_code Amount
31-1-2018 NN001 100 
31-1-2018 NN002 30 
31-1-2018 NN003 200 
31-1-2018 NN004 70 
28-2-2018 NN001 20 
28-2-2018 NN002 50 
28-2-2018 NN003 300 
28-2-2018 NN004 150 
31-3-2018 NN001 179 
31-3-2018 NN002 194 
31-3-2018 NN003 208 
31-3-2018 NN004 222 
](delimiter is spaces);
LOAD *,region_allocation*Amount as Amount1
Resident [Table 1];
Drop Table [Table 1];
Generic
LOAD Date,Staff_code,Amount,Region,Amount1
Resident [Table 2];
Drop Table [Table 2];

Regards,

Antonio

[effinty2112]

Hi Hans,

Maybe this straight table?

Date	Staff code	Amount	Reg A	Reg B
		1723	829.4	562.1
31-3-2018	NN001	179	35.8	143.2
31-3-2018	NN002	194	194	0
31-3-2018	NN003	208	145.6	62.4
31-3-2018	NN004	222	0	55.5
31-1-2018	NN001	100	20	80
31-1-2018	NN002	30	30	0
31-1-2018	NN003	200	140	60
31-1-2018	NN004	70	0	17.5
28-2-2018	NN001	20	4	16
28-2-2018	NN002	50	50	0
28-2-2018	NN003	300	210	90
28-2-2018	NN004	150	0	37.5

REG A = Alt(Amount* only({$<Region = {'REG A'}>}[region allocation]),0)

REG B = Alt(Amount* only({$<Region = {'REG B'}>}[region allocation]),0)

Regards

Andrew

[Kushal_Chawda]

Create either straight or pivot table

Dimension:

Date

Staff Code

Expression:

1) Amount

    =sum(Amount)

2) REG A

    =sum(Amount)* Only({<Region={'REG A'}>}region_allocation)

3) REG B

    =sum(Amount)* Only({<Region={'REG B'}>}region_allocation)

[qlikhans]

Dear all

apparently I was caught in a mind trap. My assumption was that I needed to create a key like NN001_REG_A. Multiple Roads lead to Rome ....

I am sorry, but selecting more answers as "the correct one" is not possible

Thank you all for your support

krgrds Hans

[eduardo_dimperio]

We don't do that to get a green star "as correct", we do to help. Choose one and gg