Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
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
Master II
Master II

@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
Master II
Master II

@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
Master II
Master II

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