topic Cecking date in Script in QlikView

Cecking date in Script

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Cecking date in Script

jubarrosor — Thu, 04 Oct 2018 09:34:18 GMT

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

Re: Cecking date in Script

Janneke — Thu, 04 Oct 2018 09:51:35 GMT

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

Re: Cecking date in Script

mhmmd_srf — Thu, 04 Oct 2018 10:10:33 GMT

how to create this variable which will hold multiple values.

Re: Cecking date in Script

cmorri1988 — Thu, 04 Oct 2018 10:53:24 GMT

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;

Re: Cecking date in Script

Janneke — Thu, 04 Oct 2018 11:57:54 GMT

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.