Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I need help to find resolutions from this issue..
I have a table consists of transaction-date, input-date, and interval
I want to count how many days from the transaction to input date excluding holidays, so from interval 4 days, I expected its only 1 day.
I've tried using NetworkDays() function, but apparently I unable to get the result I need
I tried to write like this:
NetworkDays(Transaction_date,Input_Date,Holidays)
Also I tried to count using set Expression, but apparently it will not work per line basis:
holiday)
Any Idea ?
Thank you so much
Hi
Networkdays function use the default format date DD/MM/YYYY
So first chek if you specify it in the script :
SET DateFormat='YYYY-MM-DD';
then the third argument for holidays date expect this list format : '2023-01-23','2023-01-22', ....
So you need to adapt your formula like this
NetworkDays(Transaction_date,Input_Date,concat(chr(39)& Holidays& chr(39),','))
Or create a variable
tmp_Hollidays:
LOAD concat(chr(39)& Holidays& chr(39),',') as Holidays
from ...
Let vHolidays = fieldvalue('Holidays'),1);
drop table tmp_Hollidays;
then use this function
NetworkDays(Transaction_date,Input_Date,$(Holidays))
Hope it helps
Hi
Networkdays function use the default format date DD/MM/YYYY
So first chek if you specify it in the script :
SET DateFormat='YYYY-MM-DD';
then the third argument for holidays date expect this list format : '2023-01-23','2023-01-22', ....
So you need to adapt your formula like this
NetworkDays(Transaction_date,Input_Date,concat(chr(39)& Holidays& chr(39),','))
Or create a variable
tmp_Hollidays:
LOAD concat(chr(39)& Holidays& chr(39),',') as Holidays
from ...
Let vHolidays = fieldvalue('Holidays'),1);
drop table tmp_Hollidays;
then use this function
NetworkDays(Transaction_date,Input_Date,$(Holidays))
Hope it helps
Hi,
it worked out.. Contained the holiday list through concat and using vHolidays as holiday in networkdays.