Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
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?
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?
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;
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;
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;