Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
In my data set i have only one date column . I am creating one composite key (concatenating few columns ) as
distintct xx &'-'& yy &'-'& zz as key
in the fact table.
then creating one table to calculate day difference
temp:
load
key,
min(date),
max(date),
NetWorkDays(DATE(MAX(DATE),'DD/MM/YYYY'),DATE(MIN(DATE),'DD/MM/YYYY')
resident table1;
when loading this script , i am getting syntax error . I have tried this NetWorkDays(max(date),min(date)) but same error.
In the front end i have created a chart and used the above formula as expression , however it does not gives any error but is giving wrong number e.g max(date) =12-02-2019 and min (date) = 12-02-2019 , return same and networkdays return =1 .
Could you please help me where i am doing wrong.
You are mixing case (DATE is not the same field as date), and missing a group by on the load. I would do it like this:
temp: load *, NetWorkDays(minDate, maxDate) as nwdays; load key, min(date) as minDate, max(date) as maxDate, resident table1 group by key;
Hi,
you have a missing bracket on your script:
NetWorkDays( DATE(MAX(DATE),'DD/MM/YYYY') , DATE(MIN(DATE),'DD/MM/YYYY') '==> you forgot this one )'
Thank you Prashant, it worked.
I noticed the network days is not calculating the correct number of days, for example. it is calculating 1 day between
01/01/2019 and 01/01/2019 …. (dd/mm/yyyy)
You are mixing case (DATE is not the same field as date), and missing a group by on the load. I would do it like this:
temp: load *, NetWorkDays(minDate, maxDate) as nwdays; load key, min(date) as minDate, max(date) as maxDate, resident table1 group by key;
@ashis wrote:[…]I noticed the network days is not calculating the correct number of days, for example. it is calculating 1 day between
01/01/2019 and 01/01/2019 …. (dd/mm/yyyy)
This is the correct calculation as you have not listed January 1 as a holiday.
The networkdays function returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holiday.
Hi,
Thank you for your reply.
as suggested , I am using the following syntax;
temp:
LOAD*,
NetWorkDays(STARTDATE,LASTDATE) AS AGE;
load
AdjustmentKey,
min(DATE) AS STARTDATE,
max(DATE) AS LASTDATE
Resident table
Group by Key;
However the AGE field that is getting calculated in the temp table is seems incorrect. Same day difference is counting 1 , where as if I calculate the this in front end
Interval(DATE(LASTDATE,'DD/MM/YYYY') - DATE(STARTDATE,'DD/MM/YYYY'),'D')
it shows 0 for same day.
Is something I am doing wrong .
Hi Vegar,
Thank you for your input. It makes sense.