Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I would like to create a table of staff costs from 3 tables:
- a hiring table with the year at the date of hiring
- a table with salary data
- a table with the calendar for which I want to create the budget.
I first try to join the hiring table with my calendar by creating only the months from which people are hired.
But that does not work.
In the end, I want a table of 90 records: 36 for Dupont, 32 for Durand and 22 for martin.
I enclose the QV file and the final table.
Can someone help me for this?
Thank you very much,
Arnault
Maybe this,
RESULT_TABLE:
LOAD
NoEmployee,
Name,
Begin_Year as Year,
Begin_Montth as Month
resident Employees
Left Join(RESULT_TABLE)
load
*
resident Pay
Left Join(RESULT_TABLE)
load
*
resident Calendar
Thank you Eduardo for your reply,
It's a good start but durand is only hired in May 2018 (32 months) and Martin in March 2019 (22 months).
So your table RESULT_TABLE must have 90 lines only.
Yours has 108 ...
Arnault
the solution may be to join the calendar table from which the periods corresponding to each emplyee must be extracted.
that's what I tried in this code but it does not work.
NoConcatenate
hiring:
LOAD NoEmployee,
Name,
worktime,
begin_year,
begin_month,
((begin_year-1)*12)+begin_month as NoHir
resident Employees;
for i = 0 to NoOfRows('hiring')-1
let vNoHir=peek ('NoHir','$(i)','hiring') ;
TEMPORY:
load peek ('NoEmployee','$(i)','hiring') as NoEmployee,
peek ('Name','$(i)','hiring') as Name,
peek ('worktime','$(i)','hiring') as worktime ;
left join
load year,month resident Calendar where NoCal >= $(vNoHir);
next i;
drop table hiring;
drop table Calendar;
drop table Pay;
drop table Employees;
Does somebody have an idea?
Arnault
Understand,
So after my first code put this, just to check:
NoConcatenate
Aux_Table
Distinct
*
resident Result_table;
Drop table Result_table,pay,Calendar,Employees
sorry, but I still have 108 lines ...
This code was supposed to do exactly what?
Verify if have duplicated values, but strange couse Left Join use like main table Employees, if Employees have 90 lines it should remain 90 lines, not up to 108
No in fact :
Emplyees has 3 lines
calendar has 36 lines. (from 1/2018 to 12/2020)
your solution creates a cartesian product 3x36=108
but, depending on the start date of the emplyees table, I need only bring back:
36 lines from calendar for durant (begin = 1/2018)
32 lines from calendar for dupont (begin = 3/2018)
22 lines from calendar for martin (begin = 5/2019)
I hope to be clear ...
thank you very much
But cartesian produt will occur only if you dont have common values between two tables, if you had changed your filed begin_month to month and begin_year to year, it should take only the common months and years between this tables
Eduardo,
year+month (from Calendar) must be >= than begin_year and begin_month (from Employee)
how to do that with a join ?