topic Re: ApplyMap in QlikView

ApplyMap

Frank_Hartmann — Wed, 25 Nov 2020 16:16:04 GMT

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"

Re: ApplyMap

sunny_talwar — Tue, 03 Oct 2017 14:42:09 GMT

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?

Re: ApplyMap

antoniotiman — Tue, 03 Oct 2017 14:48:51 GMT

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

sunny_talwar — Tue, 03 Oct 2017 14:49:05 GMT

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;

Re: ApplyMap

Clever_Anjos — Tue, 03 Oct 2017 14:54:26 GMT

Use this

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;