Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Check the attached App
On UI part or in Back end you required to do this.
Check the attached App
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
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
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
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
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)
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
We don't do that to get a green star "as correct", we do to help. Choose one and gg