Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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