Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
mhmmd_srf
Creator II
Creator II

Cecking date in Script

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

5 Replies
jubarros
Luminary Alumni
Luminary Alumni

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
Creator
Creator

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
Creator II
Creator II
Author

how to create this variable which will hold multiple values.

cmorri1988
Creator
Creator

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
Creator
Creator

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.