Cecking date in Script

[mhmmd_srf]

Hi All,

I have two fields CreateDate and BusBreachDate in one table.

And I have another holiday table, where all holidaydate of a year is listed.

I want to write a code like below:

if(CreateDate or BusBreachDate in (holidaydate), nextday morning 10 AM of BusBreachDate, else BusBreachDate)  as BusBreachDate_New

Example,

For a record,

CreateDate = 04-07-2018  13:37:39

BusBreachDate = 05-07-2018  04:20:27

Holidaydate =  04-07-2018

As one of the date (CreateDate  OR BusBreachDate)  falls under Holiday,

Output will be = 06-07-2018  10:00:00 AM

Can anyone please help me to implement this in qlik.

Thanks,

Sarif

[jubarrosor]

Hi Mohamad:

You can create flags to evaluate if one register is in holiday, for example using a Applymap function.

Best Regards,
Juan P Barroso

[Janneke]

Hi Mohanmad,

You can create a variable containing the holidays (use chr(39) and ',' to separate the days). Then use the Match() function. Try something like:

If(Match(CreateDate,$(vHoliday)) or Match(BusBreachDate,$(vHoliday))

,Timestamp#(BusBreachDate+1&' 10:00','DDMMYYYY hh:mm')

,BusBreachDate) as BusBreachDate_New

Greetings, Janneke

[mhmmd_srf]

how to create this variable which will hold multiple values.

[cmorri1988]

Hi there,

This solution is tried and tested - please note that I have made assumptions on the date formats being DD-MM-YYYY, please change to MM-DD-YYYY where appropriate.

Please also note that the first two load statements should be changed to load in our source data.

Hope this helps

///////////////////////

//LOAD IN CREATEDATE AND BUSBREACHDATE, CREATE DATE FORMAT TO MATCH HOLIDAYDATE FORMAT
Dates:
LOAD
    CreateDate,
    BusBreachDate,
    date(FLOOR(CreateDate),'DD-MM-YYYY') AS CreateDateFormat,
    date(FLOOR(BusBreachDate),'DD-MM-YYYY') AS BusBreachDateFormat
FROM [lib://D (qlik-sense_administrator)\Dates.xlsx]
(ooxml, no labels, table is Dates);

//LOAD IN HOLIDAYS
Holidays:
LOAD
    Holidaydate
FROM [lib://D (qlik-sense_administrator)\Dates.xlsx]
(ooxml, no labels, table is Holiday);

//LEFT JOIN 'YES' FLAG WHERE HOLIDAY DATE THE SAME AS BUSBREACHDATE
Left Join (Dates)
load
Holidaydate AS BusBreachDateFormat,
'YES' AS BusBreachDate_On_Holiday?
Resident Holidays;

//LEFT JOIN 'YES' FLAG WHERE HOLIDAY DATE THE SAME AS CREATEDATE
Left Join (Dates)
load
Holidaydate AS  CreateDateFormat,
'YES' AS CreateDate_On_Holiday?
Resident Holidays;

//CREATE NEW BUSBREACHDATE WHERE FLAG = YES IN EITHER OF ABOVE TWO LEFT JOINS
Final:
LOAD
CreateDate,
BusBreachDate,
IF(BusBreachDate_On_Holiday? = 'YES' or CreateDate_On_Holiday? = 'YES',
TIMESTAMP(BusBreachDateFormat + 1,'DD-MM-YYYY  10:00:00'),BusBreachDate) AS BusBreachDate_New
Resident Dates;

DROP Table Dates;

[Janneke]

Hi Mohammad,

Please try this:

HolidayDate_tmp:

Load Concat(Chr(39)&HolidayDate&Chr(39),',') as HolidayDate

From YOURHOLIDAYDATETABLE

;

Let vHoliday=Peek('HolidayDate',0,'HolidayDate_tmp'); 

Drop Table HolidayDate_tmp;

Hope this works. Janneke.