ApplyMap on Master Calendar is not working

[#1]

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

[#2]

Hi Rodrigo,

I'm not sure that, this solution will suits ur problem, try this...

ApplyMap('WorkHours',  Month(Date(TempDate,'YYYYMM'),'666')) AS WorkHours,

[er_mohit]

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

[#4]

Month, only return the Month Name, I need it also with the year.

[#5]

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

[#6]

Works, but only in the first date of each month... and not in every days of that period. Weird

[#7 · Accepted solution]

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,