Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

ApplyMap on Master Calendar is not working

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

1 Solution

Accepted Solutions
Not applicable

Re: ApplyMap on Master Calendar is not working

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,

6 Replies
Not applicable

Re: ApplyMap on Master Calendar is not working

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
Honored Contributor II

Re: ApplyMap on Master Calendar is not working

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

Not applicable

Re: ApplyMap on Master Calendar is not working

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

Not applicable

Re: ApplyMap on Master Calendar is not working

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

Not applicable

Re: ApplyMap on Master Calendar is not working

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

Not applicable

Re: ApplyMap on Master Calendar is not working

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,

Community Browser