Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
WorkHours:
Mapping LOAD * INLINE [
Month, Hours
201301, 186
201302, 152
201303, 160
201304, 160
201305, 176
201306, 144
201307, 176
201308, 168
201309, 168
201310, 176
201311, 168
201312, 168
];
Calendar:
LOAD
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.
Thx
Sorry for the delay...too busy at work.
It seems that Date( ) uses full date format DD.MM.YYYY thats why only worked on the first day of each month.
I concatenate string without the use of Date to make it work:
ApplyMap('WorkHr', right(TempDate,4) & mid(TempDate,4, 2 ),'666') AS WorkHours,
Hi Rodrigo,
I'm not sure that, this solution will suits ur problem, try this...
ApplyMap('WorkHours', Month(Date(TempDate,'YYYYMM'),'666')) AS WorkHours,
Try to Ist load through inline Month and Hours
like this
WorkHours:
LOAD * INLINE [
Month, Hours
201301, 186
201302, 152
201303, 160
201304, 160
201305, 176
201306, 144
201307, 176
201308, 168
201309, 168
201310, 176
201311, 168
201312, 168
];
then
WorkHr:
mapping load
Date(Date#(Month,'YYYYMM'),'YYYYMM') as MONTH,Hours AS HR
resident WorkHours;
Drop table WorkHours ;
then in master Calendar applymap on this table
like this
Calendar:
LOAD
Date(TempDate) AS CalendarDate,
Date(TempDate,'YYYYMM') AS Period,
ApplyMap('WorkHr', Date(Date#(TempDate, 'YYYYMMDD'),'YYYYMM'),'666') AS WorkHours,
because during mapping one field is primary and qlikview doent know its format hope it helps
hope it helps
Month, only return the Month Name, I need it also with the year.
Hi Rodrigo,
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.
LOAD
...
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.
Kind Regards,
Borja
Works, but only in the first date of each month... and not in every days of that period. Weird
Sorry for the delay...too busy at work.
It seems that Date( ) uses full date format DD.MM.YYYY thats why only worked on the first day of each month.
I concatenate string without the use of Date to make it work:
ApplyMap('WorkHr', right(TempDate,4) & mid(TempDate,4, 2 ),'666') AS WorkHours,