Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qlikhans
Contributor

how to allocate these amounts

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 codeRegionregion allocation
NN001REG A20%
NN001REG B80%
NN002REG A100%
NN003REG B30%
NN003REG A70%
NN004REG B25%

 

Table 2:
DateStaff codeAmount
31-1-2018NN001                   100
31-1-2018NN002                     30
31-1-2018NN003                   200
31-1-2018NN004                     70
28-2-2018NN001                     20
28-2-2018NN002                     50
28-2-2018NN003                   300
28-2-2018NN004                   150
31-3-2018NN001                   179
31-3-2018NN002                   194
31-3-2018NN003                   208
31-3-2018NN004                   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
DateStaff codeAmountReg AReg B
31-1-2018NN001                   100                      20               80
31-1-2018NN002                     30                      30                -  
31-1-2018NN003                   200                    140               60
31-1-2018NN004                     70               18
28-2-2018NN001                     20 4               16
28-2-2018NN002                     50                      50                -  
28-2-2018NN003                   300                    210               90
28-2-2018NN004                   150               38
31-3-2018NN001                   179                      36             143
31-3-2018NN002                   194                    194                -  
31-3-2018NN003                   208                    146               62
31-3-2018NN004                   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

Tags (1)
1 Solution

Accepted Solutions

Re: how to allocate these amounts

Check the attached App

9 Replies

Re: how to allocate these amounts

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

Re: how to allocate these amounts

Check the attached App

Re: how to allocate these amounts

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

OP.PNG

eduardo_dimperi
Valued Contributor II

Re: how to allocate these amounts

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
Honored Contributor III

Re: how to allocate these amounts

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
Honored Contributor

Re: how to allocate these amounts

Hi Hans,

Maybe this straight table?

Date Staff code Amount Reg A Reg B
1723 829.4 562.1
31-3-2018NN00117935.8143.2
31-3-2018NN0021941940
31-3-2018NN003208145.662.4
31-3-2018NN004222055.5
31-1-2018NN0011002080
31-1-2018NN00230300
31-1-2018NN00320014060
31-1-2018NN00470017.5
28-2-2018NN00120416
28-2-2018NN00250500
28-2-2018NN00330021090
28-2-2018NN004150037.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

Re: how to allocate these amounts

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
Contributor

Re: how to allocate these amounts

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_dimperi
Valued Contributor II

Re: how to allocate these amounts

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

Community Browser