ApplyMap

[Frank_Hartmann]

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"

[sunny_talwar · Accepted solution]

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?

[antoniotiman]

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; 

[sunny_talwar]

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;

[Clever_Anjos]

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;