topic Re: Holiday list for lastworkdate/networkdays not recognized in QlikView

Holiday list for lastworkdate/networkdays not recognized

Mon, 29 Feb 2016 13:34:37 GMT

Dear Experts,

I am working on a Qlikview document where I need to calculate with a "next business day" information - the challenge here is that this is for an international company and depending with which legal entity the individual data records are associated, different public holidays need to be considered.

I successfully extracted the public holidays for each entity and concatenated them, following the advice from other posts in this forum. However as I cannot work with a single "HolidayList" value and I did not want to create one variable per country, I tried to map the respective HolidayLists to the records themselves as a field value.

Holidays:
LOAD
num(floor( "Viz_Date"+0.5)) as HoliDate,
"viz_countryregionidName" as Country;
SQL SELECT *
FROM "CRM".dbo."Holiday"
ORDER BY "viz_countryregionidName","Viz_Date" asc;

LIST.Mapping:
Mapping
LOAD
Country,
concat(chr(39)&date(HoliDate)&chr(39), ', ') as HolidateList
Resident Holidays
group by Country;

The public holidays appear to concatenate correctly and get mapped to the records where I would need them for a chart expression, however when I try to calculate the next business day in my expression, the public holidays are ignored. In an example: "WonDate" would be a Friday, the following Monday is a public holiday and I would expect my expression to return Tuesday as the next business day. However I always receive the date for Monday.

timestamp(lastworkdate(WonDate+1 , 1, HoliDates))

When copying the field value as a text (without further modifications) into the expression, I do receive the date for Tuesday.

So I am starting to believe that fixed text values and variables are the only way to work with networkdays/lastworkdate.

Could you confirm if my guess is correct and if yes, would you have an idea how I could work with a single variable instead? Is there a way to populate a variable in the LOAD statement for each record that is loaded ?

Re: Holiday list for lastworkdate/networkdays not recognized

swuehl — Mon, 29 Feb 2016 20:45:37 GMT

You are right, you need to pass the list of holiday dates as comma separated arguments to the NetWorkDays() / LastWorkDate() function, you can't use a single argument field that holds a string with these holidays, comma separated.

I haven't really understood your sample code, you are using a MAPPING LOAD, but then don't use MAP or APPLYMAP to map your holidays. And I don't see the Holidates field defined in your code, but I guess that field is holding the mapped values somehow.

Re: Holiday list for lastworkdate/networkdays not recognized

Mon, 29 Feb 2016 21:05:51 GMT

Thank you very much for your fast response.

I omitted the code where I am actually mapping it to the final data - I included only this bit in case I had messed up the concat.

But then I actually already know I will be stuck with multiple variables, each containing the comma-separated holidays.

Off topic, but related to my particular challenge - can I restrict the peek function in some way? To be used on my original Holidays table, which contains all Holidays as well as the Country reference. Along the line of "peek if country = XXX". Or do I need to create individual data tables for each country I need?

Re: Holiday list for lastworkdate/networkdays not recognized

swuehl — Mon, 29 Feb 2016 21:34:44 GMT

The LookUp() function could return the holidays for a specific country.

Re: Holiday list for lastworkdate/networkdays not recognized

Tue, 01 Mar 2016 12:24:10 GMT

And it did - thank you for your advice and help!