Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts!
I'm having a problem with a project where I have multiple tables which have a lot of linking fields. I have searched about synthetic keys but I just can't figure out how to use those keys in this case. I hope you can help me.
I have 5 tables: Employees, Absences, Invoices, Salaries and Cost Centre Stucture.
My biggest problem is the cost centre structure which connects employees and invoices.
I attached some sample
Thank you in advance!
Hi Check this:
Employees:
Load *,EmployeeID&Year&Month as Key,Year&Month as Key1;
LOAD EmployeeID, Department1, Month, Year INLINE [
EmployeeID, Department1, Year, Month
1, 1, 2015, 1
1, 1, 2015, 2
1, 2, 2015, 3
2, 2, 2015, 1
2, 3, 2015, 2
2, 3, 2015, 3
];
Absences:
Load *,EmployeeID&Year&Month as Key;
LOAD EmployeeID, [Days asence], Month, Year INLINE [
EmployeeID, Days asence, Month, Year
1, 2, 1, 2015
1, 1, 3, 2015
];
Invoices:
Load *, Year&Month as Key1;
LOAD InvoiceID, [Cost Centre], Month, Year, € INLINE [
InvoiceID, Cost Centre, Month, Year, €
1, 1, 1, 2015, 500
2, 1, 1, 2015, 1000
3, 2, 2, 2015, 1500
3, 3, 2, 2015, 500
4, 4, 2, 2015, 100
5, 5, 3, 2015, 500
6, 5, 3, 2015, 400
7, 3, 3, 2015, 500
8, 4, 3, 2015, 500
];
Salaries:
Load *,EmployeeID&Year&Month as Key;
LOAD EmployeeID, Month, Year, € as Salary_€ INLINE [
EmployeeID, Month, Year, €
1, 1, 2015, 2000
1, 2, 2015, 2000
1, 3, 2015, 2000
2, 1, 2015, 3000
2, 2, 2015, 3000
2, 3, 2015, 3000
];
Cost_Centre_Structure:
LOAD [Cost Centre], [Cost Centre 2], Department, [Cost Centre 3], Year INLINE [
Cost Centre, Cost Centre 2, Department, Cost Centre 3, Year
1, 1, 1, 1, 2015
2, 2, 1, 1, 2015
3, 3, 1, 1, 2015
4, 1, 1, 1, 2015
5, 2, 1, 1, 2015
1, 3, 2, 1, 2015
2, 1, 2, 1, 2015
3, 2, 2, 1, 2015
4, 3, 2, 1, 2015
5, 1, 2, 1, 2015
];
drop Fields Month, Year from Employees;
drop Fields Month, Year,EmployeeID from Salaries;
drop Fields Month, Year,EmployeeID from Absences;
drop Fields Year from Cost_Centre_Structure;
Thank you for you quick answer! I just found out that I had a small mistake in my example. Here's a right now.
I would prob need a master calendar too?
The problem is this:
I have a cost structure which has 5 levels.I can connect invoices to the "deepest" level which is cost centre. I can also connect employees to this structure but I can connect that only on the Cost structure 3 level. Every month I get new information about the employees, invoices, etc. Cost structure updates every year.
Thanks!
-Mikael
Hi,
Check this. Hope this is what you are looking for.
SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='D.M.YYYY';
SET TimestampFormat='D.M.YYYY h:mm:ss[.fff]';
SET MonthNames='tammi;helmi;maalis;huhti;touko;kesä;heinä;elo;syys;loka;marras;joulu';
SET DayNames='ma;ti;ke;to;pe;la;su';
Employees:
Load *,EmployeeID&Year&Month as Key,Year&Month as Key1;
LOAD EmployeeID, Department1, Month, Year INLINE [
EmployeeID, Department1, Year, Month
1, 1, 2015, 1
1, 1, 2015, 2
1, 2, 2015, 3
2, 2, 2015, 1
2, 3, 2015, 2
2, 3, 2015, 3
];
Absences:
Load *,EmployeeID&Year&Month as Key;
LOAD EmployeeID, [Days asence], Month, Year INLINE [
EmployeeID, Days asence, Month, Year
1, 2, 1, 2015
1, 1, 3, 2015
];
Invoices:
Load *, Year&Month as Key1;
LOAD InvoiceID, [Cost Centre], Month, Year, € INLINE [
InvoiceID, Cost Centre, Month, Year, €
1, 1, 1, 2015, 500
2, 1, 1, 2015, 1000
3, 2, 2, 2015, 1500
3, 3, 2, 2015, 500
4, 4, 2, 2015, 100
5, 5, 3, 2015, 500
6, 5, 3, 2015, 400
7, 3, 3, 2015, 500
8, 4, 3, 2015, 500
];
Salaries:
Load *,EmployeeID&Year&Month as Key;
LOAD EmployeeID, Month, Year, € as Salary_€ INLINE [
EmployeeID, Month, Year, €
1, 1, 2015, 2000
1, 2, 2015, 2000
1, 3, 2015, 2000
2, 1, 2015, 3000
2, 2, 2015, 3000
2, 3, 2015, 3000
];
Cost_Centre_Structure:
LOAD [Cost Centre], [Cost Centre 2], Department, [Cost Centre 3], Year INLINE [
Cost Centre, Cost Centre 2, Department, Cost Centre 3, Year
1, 1, 1, 1, 2015
2, 2, 1, 1, 2015
3, 3, 1, 1, 2015
4, 1, 1, 1, 2015
5, 2, 1, 1, 2015
1, 3, 2, 1, 2015
2, 1, 2, 1, 2015
3, 2, 2, 1, 2015
4, 3, 2, 1, 2015
5, 1, 2, 1, 2015
];
LET vDateMin=num(makedate(2014,01,01));
LET vDateMax=floor(monthend(today()));
LET vDateToday = num(today());
Calender:
Load *, Year&Month as Key1;
LOAD Distinct Date($(vDateMin) + RowNo() - 1) AS Date,
month(Date($(vDateMin) + RowNo() - 1)) AS Month,
year(Date($(vDateMin) + RowNo() - 1)) AS Year,
monthname(Date($(vDateMin) + RowNo() - 1)) AS Monthname,
Week(Date($(vDateMin) + RowNo() - 1)) AS Week
AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);
drop Fields Month, Year from Employees;
drop Fields Month, Year,EmployeeID from Salaries;
drop Fields Month, Year,EmployeeID from Absences;
drop Fields Year from Cost_Centre_Structure;
drop Fields Month, Year from Invoices;
QUALIFY Year, Month,€;
Employees:
LOAD * INLINE [
EmployeeID, Department1, Year, Month
1, 1, 2015, 1
1, 1, 2015, 2
1, 2, 2015, 3
2, 2, 2015, 1
2, 3, 2015, 2
2, 3, 2015, 3
];
Absences:
LOAD * INLINE [
EmployeeID, Days asence, Month, Year
1, 2, 1, 2015
1, 1, 3, 2015
];
Invoices:
LOAD * INLINE [
InvoiceID, Cost Centre, Month, Year, €
1, 1, 1, 2015, 500
2, 1, 1, 2015, 1000
3, 2, 2, 2015, 1500
3, 3, 2, 2015, 500
4, 4, 2, 2015, 100
5, 5, 3, 2015, 500
6, 5, 3, 2015, 400
7, 3, 3, 2015, 500
8, 4, 3, 2015, 500
];
Salaries:
LOAD * INLINE [
EmployeeID, Month, Year, €
1, 1, 2015, 2000
1, 2, 2015, 2000
1, 3, 2015, 2000
2, 1, 2015, 3000
2, 2, 2015, 3000
2, 3, 2015, 3000
];
Cost_Centre_Structure:
LOAD * INLINE [
Cost Centre, Cost Centre 2, Department1, Cost Centre 3, Year
1, 1, 1, 1, 2015
2, 2, 1, 1, 2015
3, 3, 1, 1, 2015
4, 1, 1, 1, 2015
5, 2, 1, 1, 2015
1, 3, 2, 1, 2015
2, 1, 2, 1, 2015
3, 2, 2, 1, 2015
4, 3, 2, 1, 2015
5, 1, 2, 1, 2015
];
