Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: We have made updates to resolve a Page Not Found error. If you still get this error, please let us know in the Community Corner.
Frank_Hartmann
Honored Contributor

ApplyMap

Following situation:

I flag an holiday by using Applymap which works fine:

Mapping:

Mapping LOAD * Inline  [

holiday, Nr

"06.01.2017", 1

];

generateRange:

LOAD * INLINE [

    start, end

    "01.01.2017","01.03.2017"  

];

left Join

Load

date(start+IterNo()-1) as range

Resident generateRange while start+iterno()-1 <= end;

Final:

Load start,end,range,

Applymap('Mapping',range,'NA') as FLAG

Resident generateRange; DROP Table generateRange;

Now I would like to flag not only a single day but a date range e.g:

Mapping:

Mapping LOAD * Inline  [

holidaystart, holidayend

"06.01.2017", "13.01.2017"

];

generateRange:

LOAD * INLINE [

    start, end

    "01.01.2017","01.03.2017"  

];

left Join

Load

date(start+IterNo()-1) as range

Resident generateRange while start+iterno()-1 <= end;

Final:

Load start,end,range,

if(Applymap('Mapping',range,'NA')='NA',0,1) as FLAG

Resident generateRange; DROP Table generateRange;

how do i have to use applymap if i want to flag  all the days from 06.01.2017 TO  13.01.2017  ?"06.01.2017", "13.01.2017"

"06.01.2017", "13.01.2017"

1 Solution

Accepted Solutions

Re: ApplyMap

I think you will have to use the While loop in the mapping table to convert the range into individual dates. Once you do that, it should be fairly simple, right?

4 Replies

Re: ApplyMap

I think you will have to use the While loop in the mapping table to convert the range into individual dates. Once you do that, it should be fairly simple, right?

Highlighted
antoniotiman
Honored Contributor III

Re: ApplyMap

Maybe like this

Mapping:
Mapping LOAD *;
LOAD Date(holidaystart + IterNo()-1) as holidaystart,1 as Flag  Inline 
holidaystart, holidayend 
"06.01.2017", "13.01.2017" 
]
While holidaystart + IterNo()-1 <= holidayend

generateRange: 
LOAD * INLINE
start, end 
"01.01.2017","01.03.2017" 
]

left Join 
Load 
date(start+IterNo()-1) as range 
Resident generateRange while start+iterno()-1 <= end

Final: 
Load start,end,range
Applymap('Mapping',range,0) as FLAG 
Resident generateRange; DROP Table
generateRange; 

Re: ApplyMap

May be like this

Mapping: 

Mapping

LOAD Date(holidaystart + IterNo() - 1) as Holiday,

1 as Flag

While holidaystart + IterNo() - 1 <= holidayend;

LOAD * Inline  [ 

holidaystart, holidayend 

"06.01.2017", "13.01.2017" 

];

generateRange:

LOAD *,

Date(start+IterNo()-1) as range

While start + IterNo()-1 <= end;

LOAD * INLINE [ 

start, end 

"01.01.2017","01.03.2017"   

];

Final: 

LOAD start,

end,

range, 

ApplyMap('Mapping', range, 0) as FLAG 

Resident generateRange;

DROP Table generateRange;

Employee
Employee

Re: ApplyMap

Use this

h:

load

date(holidaystart + IterNo() - 1) as date

While IterNo() <= num(holidayend) - num(holidaystart) +1;

LOAD * Inline  [ 

holidaystart, holidayend 

"06.01.2017", "13.01.2017" 

];

Mapping load date,1 resideent h;

drop table h;