Create holiday-string for networkdays-function from holiday-calendar in SAP

[#1]

Hi,

I use the SAP-holiday-calendar to extract the holidays for the regions necessary for our customer (a multinational), and a macro that creates a string of the holidays in the syntax necessary for the networkdays-command (", '2010-01-01', ...") and then writes that string into a textfile.
This textfile can then be used in the networkdays-function: networkdays (monthstart([Invoice date]), [Invoice date] $(Include=..\sap_common\holidays_nl.txt))

My problem is that QlikView is getting more and more restrictive about macros. We can't place the loop to extract the dates for the different regions and the macro to create the textfile in the same application, since QlikView allows a Server-Macro-Execution only upon starting an application, so I'd have to create an application for holiday-extraction and an application for the macro-execution for each holiday-calendar - which is a bit difficult to explain to the customer ("You have to maintain 6 different applications for the exact same purpose because...")
Also, so far we haven't managed to get it to work under QlikView 10 (which is a bit of a puzzler, since it uses the syntax for logfile-export, which should still be working under any QV-version). So, the days of the macro are obviously numbered.

Has anyone else hit the same problem and come up with a more elegant (macroless) solution to create a string with the holiday-dates? I need something that updates the dates automatically, since I won't be around much longer to do manual updates.

Thanks for any input,
kind regards,
Lenka

[pover]

Lenka,

You should also be able to generate the string in the script with something like the following (not tested). Regards.

Holidays:
LOAD Holiday
FROM
Holidays.xlsx
(ooxml, embedded labels, table is Sheet1);

Let vNroRegistros = NoOfRows(Holidays);

For vI = 0 to (vNroRegistros - 1)

if vI=0 then
Let vHolidays = chr(39) & Peek('Holiday',vI,'Holidays');
elseif vI<(vNroRegistros - 1) then
Let vHolidays = $(vHolidays) & ', ' & Peek('Holiday',vI,'Holidays');
else
Let vHolidays = $(vHolidays) & ', ' & Peek('Holiday',vI,'Holidays') & chr(39);
end if

Next

[johnw · Accepted solution]

Someone pointed out recently that it was easier to make a list with concat() instead of with a loop. So I recently revised my old example to load a holiday list that way. See attached.

This does NOT include the creation of the text file with the list of holidays. Not sure how you go about that part. I would think you'd just load from an SAP holiday table, but I know next to nothing about SAP, so I'm no help there, and it sounds like that might be where you're having trouble, in which case my example is useless to you.

Still, in case it's useful...

[#4]

Hi,

thank you both so much for your help. I finally settled on Johns method, using the following code in my script to create the Workday-no of each date + the total amount of workdays in that month:

Holidays:
load concat(Holiday, ',') as Holidays
from
.. \..\..\SAP\2_Consolidation\C_Cal_Holidays.qvd
(qvd)
where Region = 'D_NW'
;

let vNetWorkDays = 'networkdays($1, $2, ' & peek('Holidays') & ')';
drop table Holidays;

Calendar:
load
[Invoice date],
…
$(vNetWorkDays(monthstart([Invoice date]), [Invoice date])) as Workday,
$(vNetWorkDays(monthstart([Invoice date]), monthend([Invoice date]))) as [Workdays total],
…
resident Transactions;

Amazingly enough, I just extracted the whole list of holidays from SAP (from 01.01.2005 upwards) and networkdays did not have a problem processing the endless list.

Have a good night (I was really surprised at your late postings, until I saw the timezone you are in),
best wishes,
Lenka

[johnw]

lenkacfk wrote:Amazingly enough, I just extracted the whole list of holidays from SAP (from 01.01.2005 upwards) and networkdays did not have a problem processing the endless list.

That IS amazing. I'm surprised but happy that it was able to handle that many dates at once.