Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi -
I have the following code in my dataload editor :
MAJORLCALENDAR:
NoConcatenate Load
MLID,
RAMOUNT,
RCALENDARDATE,
DATENUMBER
resident MAJORL01
where not match(DATENUMBER,'Sat','Sun');
Note : RCALENDARDATE is in 'mm/dd/yyyy' format
If I have an excel sheet or google sheet with my holiday dates
example:
HDAY
11/26/2020
11/27/2020
etc...
How do I remove the Holidays from the MAJORLCALENDAR table given I have the specific days in the HDAY table ?
Thanks - Jerry
@jerryr125 you can use for example mapping table like :
MapHoliday:
mapping load HDAY,'Y' as ISHOLIDAY
inline [
HDAY
11/26/2020
11/27/2020
];
MAJORLCALENDAR:
NoConcatenate
load * where todelete='N';
Load
MLID,
RAMOUNT,
RCALENDARDATE,
DATENUMBER,
applymap('MapHoliday',RCALENDARDATE,'N') as todelete
resident MAJORL01
where not match(DATENUMBER,'Sat','Sun');
drop fields todelete;
this is an example :
MapHoliday:
mapping load HDAY,'Y' as ISHOLIDAY
inline [
HDAY
11/26/2020
11/27/2020
];
MAJORLCALENDAR:
load * where todelete='N';
Load
MLID,
RAMOUNT,
RCALENDARDATE,
DATENUMBER,
applymap('MapHoliday',RCALENDARDATE,'N') as todelete
inline [
MLID,RAMOUNT,RCALENDARDATE,DATENUMBER
1,1,11/26/2020,1
2,2,11/27/2020,2
3,3,01/01/2020,3
4,3,01/07/2020,3
];
drop fields todelete;
output:
Hi Jerryr125
Check the below script.
Holiday:
Load * Inline
[
HDate
02/01/2020
];
Fact:
Load * Inline
[
Date
01/01/2020
02/01/2020
03/01/2020
04/01/2020
05/01/2020
06/01/2020
]
Where not Exists(HDate, Date);
@jerryr125 you can use for example mapping table like :
MapHoliday:
mapping load HDAY,'Y' as ISHOLIDAY
inline [
HDAY
11/26/2020
11/27/2020
];
MAJORLCALENDAR:
NoConcatenate
load * where todelete='N';
Load
MLID,
RAMOUNT,
RCALENDARDATE,
DATENUMBER,
applymap('MapHoliday',RCALENDARDATE,'N') as todelete
resident MAJORL01
where not match(DATENUMBER,'Sat','Sun');
drop fields todelete;
this is an example :
MapHoliday:
mapping load HDAY,'Y' as ISHOLIDAY
inline [
HDAY
11/26/2020
11/27/2020
];
MAJORLCALENDAR:
load * where todelete='N';
Load
MLID,
RAMOUNT,
RCALENDARDATE,
DATENUMBER,
applymap('MapHoliday',RCALENDARDATE,'N') as todelete
inline [
MLID,RAMOUNT,RCALENDARDATE,DATENUMBER
1,1,11/26/2020,1
2,2,11/27/2020,2
3,3,01/01/2020,3
4,3,01/07/2020,3
];
drop fields todelete;
output:
@jerryr125 you can use for example mapping table like :
MapHoliday:
mapping load HDAY,'Y' as ISHOLIDAY
inline [
HDAY
11/26/2020
11/27/2020
];
MAJORLCALENDAR:
NoConcatenate
load * where todelete='N';
Load
MLID,
RAMOUNT,
RCALENDARDATE,
DATENUMBER,
applymap('MapHoliday',RCALENDARDATE,'N') as todelete
resident MAJORL01
where not match(DATENUMBER,'Sat','Sun');
drop fields todelete;
this is an example :
MapHoliday:
mapping load HDAY,'Y' as ISHOLIDAY
inline [
HDAY
11/26/2020
11/27/2020
];
MAJORLCALENDAR:
load * where todelete='N';
Load
MLID,
RAMOUNT,
RCALENDARDATE,
DATENUMBER,
applymap('MapHoliday',RCALENDARDATE,'N') as todelete
inline [
MLID,RAMOUNT,RCALENDARDATE,DATENUMBER
1,1,11/26/2020,1
2,2,11/27/2020,2
3,3,01/01/2020,3
4,3,01/07/2020,3
];
drop fields todelete;
output:
excellent !! thank you !!!!