topic Add months to salary in QlikView

Add months to salary

Thu, 25 Feb 2016 16:04:41 GMT

Hi,

I'm new to Qlik and trying to acomplish the following.

I have a table with employees and year salary's. I also have a master calendar and some other tables.

This is my master calendar

minmax:
LOAD
   date(max(OrderDate)) as MaxDate
RESIDENT Facttable;


LET vMinDate = Num(makedate($(vDateLimit)));
LET vMaxDate = Num(peek('MaxDate'));


DROP TABLE minmax;


TempCalendar:
LOAD
date($(vMinDate) + rowno() -1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;


MasterCalendar:
Load
TempDate As OrderDate,
'Q-' & Ceil(Month(TempDate)/3) as Quarter,
Week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month
resident TempCalendar;

This is my Employees table

Employees:
LOAD EmployeeID,
  FullName,
  [Year Salary]
RESIDENT EmployeesTmp;

I want to make a new calendar, with the employeeID's, Salary's per month and with them linking to the OrderDate of the mastercalendar. How do i accomplish this?

Thanks in advance.

Re: Add months to salary

Gysbert_Wassenaar — Thu, 25 Feb 2016 16:48:35 GMT

Where are the salaries per month for the employees?

Re: Add months to salary

Thu, 25 Feb 2016 16:50:48 GMT

They do not exist yet. I only have a year salary in my source.

Re: Add months to salary

Gysbert_Wassenaar — Thu, 25 Feb 2016 17:20:24 GMT

Then you have to decide how to turn the year salary into month salaries. And which year are we talking about. I didn't see a year field either. Or does the year salary apply to all years for the entire time span in the master calendar?

Re: Add months to salary

Wed, 02 Mar 2016 10:29:50 GMT

Hi There,

I've found a solution:)

It indeed had to go the full span of the master calendar. So first i created a temp table where with iterno() i've generated the 12 months, and after that in the defenitive table the years (also with iterno())