create a salary budget table

[doespirito]

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

[eduardo_dimperio]

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

[doespirito]

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

[doespirito]

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

[eduardo_dimperio]

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

[doespirito]

sorry, but I still have 108 lines ...
This code was supposed to do exactly what?

[eduardo_dimperio]

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

[doespirito]

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

[eduardo_dimperio]

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

[doespirito]

Eduardo,

year+month (from Calendar) must be >= than begin_year and begin_month (from Employee)

how to do that with a join ?