topic Re: remove holidays from calendar table in App Development

remove holidays from calendar table

jerryr125 — Sat, 16 Nov 2024 18:04:47 GMT

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

Re: remove holidays from calendar table

girish21595 — Tue, 22 Sep 2020 13:30:32 GMT

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

Re: remove holidays from calendar table

Taoufiq_Zarra — Tue, 22 Sep 2020 13:33:24 GMT

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

Re: remove holidays from calendar table

Taoufiq_Zarra — Tue, 22 Sep 2020 13:33:27 GMT

@jerryr125 you can use for example mapping table like :

this is an example :

output:

Re: remove holidays from calendar table

jerryr125 — Tue, 22 Sep 2020 19:06:19 GMT

excellent !! thank you !!!!