Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
jerryr125
Professor-Ambassador
Professor-Ambassador

remove holidays from calendar table

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

Labels (3)
1 Solution

Accepted Solutions
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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
girish21595
Partner
Partner

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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

jerryr125
Professor-Ambassador
Professor-Ambassador
Author

excellent !! thank you !!!!