remove holidays from calendar table

[jerryr125]

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

[girish21595]

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);

[Taoufiq_Zarra]

@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:

[Taoufiq_Zarra · Accepted solution]

@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]

excellent !! thank you !!!!