Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Any help will be highly appreciated!
Many thanks in advance
Tobias
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.
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.
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
I found the error myself: "Rechnungsjahr" needs to be included in the "group by". Sorry!