topic Load holiday list into variable in script in QlikView

Load holiday list into variable in script

Mon, 27 Apr 2015 09:56:13 GMT

Hi,

in order to use the below function I would like to load a list of holidays into a variable.

FirstWorkDate(Today(), 1, $(vMyHolidays))

Im stuck.

Any ideas?

Attaching example data and QV-doc.

//O

Re: Load holiday list into variable in script

NareshGuntur — Mon, 27 Apr 2015 10:16:41 GMT

Olle,

If you are trying to get the start days for all the holidays, you should be using a for loop so that all the holiday values will be included. If this is not clear, please explain more about the final output you are interested in.

Best Regards,

Naresh

Re: Load holiday list into variable in script

tresB — Mon, 27 Apr 2015 10:22:24 GMT

Try defining your variable like:

=Chr(39)&Concat(Holidays,chr(39)&','&chr(39))&chr(39)

In the fornt-end

Re: Load holiday list into variable in script

NareshGuntur — Mon, 27 Apr 2015 10:27:11 GMT

Add this in the script

set vMyHolidays = "max(Holiday)";

where Holiday is the name of the field which contains the list of holidays.

Best Regards,

Naresh

Re: Load holiday list into variable in script

jagan — Mon, 27 Apr 2015 10:33:12 GMT

Hi,

Try this expression

=FirstWorkDate(Today(), 1, Chr(39) & Concat(Holidays, Chr(39)&','&Chr(39))&Chr(39))

Regards,

Jagan.

Re: Load holiday list into variable in script

Anonymous — Mon, 27 Apr 2015 11:19:21 GMT

try loading your master calendar like this :

//******* Holiday********

tblHoliday:

LOAD Date(Holiday) as Holiday;

SQL SELECT Holiday

FROM table1;

/************** varHoliday **************/

tmpTbl:

NoConcatenate

LOAD Concat(Holiday,',') as fieldHoliday

Resident tblHoliday;

LET varHoliday = Peek('fieldHoliday',0,'tmpTbl');

DROP Table tmpTbl;

//Script One

Let varMinDate = num(MakeDate(2000,1,1));

Let varMaxDate = num(MakeDate(2020,12,31));

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(varMinDate) + rowno() - 1 AS Num,

date($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

$(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************

MasterCalendar:

LOAD TempDate AS CalDate,

Date(TempDate) AS CalanderDate,

If(weekday(TempDate)='Sat' or weekday(TempDate)='Sun' or If(substringcount('$(varHoliday)',Date(TempDate))>0,1,0),0,1) as HolidayFlag

1 As PlannedBD,

resident TempCalendar

ORDER BY TempDate ASC;

then use holidayflag to check if it is holiday or not in your calculation.

Re: Load holiday list into variable in script

Mon, 27 Apr 2015 11:24:51 GMT

@Jagan and Naresh,

tried to implement your suggested solutions but holidays are still not calculated for

For instance, for "Date"m 2-jan, prev date should be 30-Dec

Re: Load holiday list into variable in script

Mon, 27 Apr 2015 11:27:09 GMT

Hi balkumarchandel.

Im calling an add in kind of master calendar for all of my apps so your solution is not appropriate for me im afraid.

Thanks though!

Re: Load holiday list into variable in script

NareshGuntur — Mon, 27 Apr 2015 12:10:24 GMT

For Date 02-Jan-15, there's no holiday associated. So, it doesn't consider 01-Jan-15 as a holiday

Cheers,

Naresh

Re: Load holiday list into variable in script

Mon, 27 Apr 2015 12:15:11 GMT

Sorry, my bad!

Now it should be updated accordingly.

But still no change as far as I can see.

Regards,

Olle

Re: Load holiday list into variable in script

Mon, 27 Apr 2015 12:17:58 GMT

Maybe this could help.

Calculating Holidays in script

Re: Load holiday list into variable in script

NareshGuntur — Mon, 27 Apr 2015 12:24:59 GMT

01-Jan still is not labeled as holiday.

Please check this dashboard. Working fine if 01-Jan is declared in holiday list.

Cheers,

Naresh

Re: Load holiday list into variable in script

Mon, 27 Apr 2015 12:31:50 GMT

Ok, but pls see below. Then for date 2-jan, the answer should be 30-dec 2014 right?

Now it says 31-dec-2014 even though it is also declared as a holiday in the list.

Do you know why?