topic Re: Network of days with holidays in App Development

Network of days with holidays

Lesor — Wed, 19 Apr 2023 07:51:48 GMT

Hi,

I'm trying to get the aging, but in the script below it does not inlcude holidays.

Can anyone point to m what I did wrong?

[Holidays]:
LOAD
DATE("HOLIDAY_DATE",'MM/DD/YYYY') as HolidayDte
;
SQL

SELECT HOLIDAY_DATE FROM HOLIDAY_TABLE;

LET NumRows=NoOfRows('Holidays');
For i=0 to NumRows
LET vHolidays= Concat(Peek('HolidayDte',$(i),'Holidays'),',');
Next;

[AGING_TABLE]:

LOAD

ORDER,

NetWorkDays(TXNDATE,today(),'$(vHolidays)') AS AGING;

SQL

SELECT ORDER, TXNDATE FROM Table1;

Re: Network of days with holidays

edwin — Fri, 21 Apr 2023 19:52:03 GMT

i think your problem is in the concat. you should use that in aggregation. also i wouldnt use a loop, because you need to start testing if its the first entry (i=0) as you need to add a single quote (chr 39) to the first instance and then a chr39 + comma+chr39 of subsequent entries + the last chr39. you can do all these using an aggr:

NoConcatenate
temp:
load chr(39) & concat(HolidayDte, chr(39)&','&chr(39))&chr(39) as H
resident Holidays
;
LET vHolidays= Peek('H');
drop table temp;

also when you run your load script add a trace so you know whats happening and you can figure out where its failing:

trace $(vHolidays)

a side note, when you do a loop and you start with 0, you should end with rows-1

Re: Network of days with holidays

edwin — Fri, 21 Apr 2023 20:10:19 GMT

to add on, with this new code, vHolidays will be in the format
'MM/DD/YYYY','MM/DD/YYYY'

so in your expression to compute for networkdays, you wont need the single quotes anymore:
NetWorkDays(TXNDATE,today(),$(vHolidays)) AS AGING;

i suggest making the vHolidays complete with open and close single quote so it makes sense when you use it instead of
MM/DD/YYYY','MM/DD/YYYY