Calculation in Load Script from two different tables

[TobiasTebben]

Hello everyone!

I reckon my question is rather simple for the experts, but I cannot find a solution for it. Maybe I'm googling the wrong keywords.

I have two tables which look like this:

Table with revenues per project: [TableA]

Project	Revenue
1	1000
2	1500
3	800
4	750

Table with target fees per employee: [TableB]

Project	Employee	TargetFee
1	Bob	700
1	Rita	400
2	Andrew	1000
2	Bob	300
3	Andrew	550
3	Rita	200

In the load script, I would like to distribute the actual revenues to the employees according to the ratio of the target fees. So my TableB is supposed to look like this:

Project	Employee	TargetFee	ActualFee
1	Bob	700	636.36
1	Rita	400	363.64
2	Andrew	1000	1153.86
2	Bob	300	346.14
3	Andrew	550	550
3	Rita	200	200

So in "pseudo code", I am imagening something like this:

CONCATENATE(TableB) 
LOAD
Project,
Employee,
Revenue * "Sum(TargetFee) of specific Employee" / "Sum(TargetFee) of all Employees" AS ActualFee
RESIDENT TableA    //Problem: "TargetFee" is not in TableA?!
GROUP BY Project, Employee;

I am encountering two problems when I try to do this in my load script:

• For my calculated measure "ActualFee" I need measures from both TableA and TableB. The above CONCATENATE(TableB) LOAD ... RESIDENT TableA produces an error message because not everything needed for the calculation does in fact reside in TableA. Similar questions have been asked before in this forum (example), so I will perhaps be able to figure out how to do that, once I solve the second problem:
• When trying to calculate "ActualFee", I do not know how to discern "Sum(TargetFee) of specific Employee" and "Sum(TargetFee) of all Employees". My guess would be that I have to use aggr(), but to be honest,  so far I have failed trying to understand that function

Any help will be highly appreciated!

Many thanks in advance

Tobias

[Gabbar · Accepted solution]

TableB:
Load * from source;
inner join
load Project,Sum(TargetFee) as Total_Fee resident TableB group by Project;
inner join
TableA:
Load Project,Revenue  as fee_available from source;

noconcatenate
Table_B:
Load Project,Employee,TargetFee,(TargetFee/Total_Fee)*fee_available resident TableB;
Drop Table TableB;

I have used Inner join because i am assuming all values of Project are available in both tables.

[TobiasTebben]

Dear Akash,

many thanks for your reply. Although - as a newbie - I do not fully understand "inner join", I do understand the overall logic of your approach.

Unfortunately, I get an "invalid expression" error message in my load script for the part

inner join
TableA:
Load Project,Revenue  as fee_available from source;

My actual code is this:

Inner Join
[TableA]:  
LOAD
[AUFTRAGSNUMMER],     //Project
Sum([Gebührenbetrag]) AS FeeAvailable,
Rechnungsjahr
RESIDENT [QLIK Rechnung] group by [AUFTRAGSNUMMER];

with [AUFTRAGSNUMMER] being Project,
SUM([Gebührenbetrag]) being Revenue  (the source has multiple entries per project, therefore I have to add up the revenue per project) and
Rechnungsjahr being the fiscal year (I ommited that in my question because I thought it is independent from my problem).

I have checked to code about ten times but I cannot see the fault with it. Why  is it an invalid expression?

Thanks again
Tobias

[TobiasTebben]

I found the error myself: "Rechnungsjahr" needs to be included in the "group by". Sorry!