topic Re: create a salary budget table in QlikView

create a salary budget table

doespirito — Mon, 13 Aug 2018 16:37:46 GMT

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

Re: create a salary budget table

eduardo_dimperio — Mon, 13 Aug 2018 19:07:22 GMT

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

Re: create a salary budget table

doespirito — Tue, 14 Aug 2018 07:10:16 GMT

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

Re: create a salary budget table

doespirito — Tue, 14 Aug 2018 12:10:30 GMT

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

Re: create a salary budget table

eduardo_dimperio — Tue, 14 Aug 2018 12:29:30 GMT

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

Re: create a salary budget table

doespirito — Tue, 14 Aug 2018 12:55:16 GMT

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

Re: create a salary budget table

eduardo_dimperio — Tue, 14 Aug 2018 13:03:14 GMT

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

Re: create a salary budget table

doespirito — Tue, 14 Aug 2018 13:18:55 GMT

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

Re: create a salary budget table

eduardo_dimperio — Tue, 14 Aug 2018 13:59:58 GMT

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

Re: create a salary budget table

doespirito — Tue, 14 Aug 2018 15:34:03 GMT

Eduardo,

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

how to do that with a join ?

Re: create a salary budget table

eduardo_dimperio — Tue, 14 Aug 2018 17:09:52 GMT

Arnault,

If you use Left Join with Employee as main table you will only have year and month (from Calendar) = than begin_year and begin_month (From Employee), but if you use Calendar as main table you will have all dates and some with NoEmployee. After that you can use a where clause to filter

Re: create a salary budget table

doespirito — Thu, 16 Aug 2018 08:40:58 GMT

here is how I solved my problem.

If you have a better solution, do not hesitate to give it to me with the qv file of the first post.

Have a good day,

Arnault

Temp:

LOAD NoEmployee,

((begin_year-1)*12)+begin_month as NoHir

resident Employees;

for i = 0 to NoOfRows('Temp')-1

let vNoHir=peek ('NoHir','$(i)','Temp') ;

let vNoEmpl=peek ('NoEmployee','$(i)','Temp');

budget:

load year,month,$(vNoEmpl) as NoEmployee resident Calendar where NoCal >= $(vNoHir);

next i;

Left join(Temp2)

load NoEmployee,Name, worktime resident Employees;

Left join(Temp2)

load * resident Pay;

drop table Temp;

drop table Calendar;

drop table Pay;

drop table Employees;