I have a Mapping Table with the amount of workhours per Year and Month Period, and I wanna asociate it with the MasterCalendar, making a field in the master calendar with the amount of hours per month.
Mapping LOAD * INLINE [
Date(TempDate) AS CalendarDate,
Date(TempDate,'YYYYMM') AS Period,
ApplyMap('WorkHours', Date(Date#(TempDate, 'YYYYMMDD'),'YYYYMM'),'666') AS WorkHours,
I also tried
ApplyMap('WorkHours', Date(TempDate, 'YYYYMM'),'666') AS WorkHours,
If I hardcode the period it works... and If I see the value "Period" is the same value that I have in the Mapping Table. But only hardcoding the value it works...
ApplyMap('WorkHours', '201302','666') AS WorkHours,
Im doing this because I need to show a Chart with the productivity of an employee and because each month has a diferent amount of workable hours... I try this approach but I hear any other idea.
You should first check the result of the expression you are using to pass it wo the Applymap function. In order to to that, load it as a new field and check the internal value using a listbox or tablebox and change the Number Format to Integer to see if you are getting a full date or just an integer with YYYYMM.
Date(Date#(TempDate, 'YYYYMMDD'),'YYYYMM') as Test_Field
I'm afraid you are not getting a YYYYMM integer as the Applymap function should receive.
Try with this:
ApplyMap('WorkHours', year(TempDate) * 100 + num(month(TempDate)) ,'666') AS WorkHours,
Also, for the same results, I think you could join the WorkHours table to the Calendar.