topic Re: Networkdays function does not returning syntax error in load script in QlikView

Networkdays function does not returning syntax error in load script

ashis — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Networkdays function does not returning syntax error in load script

YoussefBelloum — Fri, 19 Apr 2019 13:14:30 GMT

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 )'

Re: Networkdays function does not returning syntax error in load script

ashis — Wed, 24 Apr 2019 03:52:02 GMT

Hi, I am sorry, I did not get you. I am using the following syntax NetWorkDays(DATE(MAX(DATE),'DD/MM/YYYY'),DATE(MIN(DATE),'DD/MM/YYYY')) as AGE syntax is fine , I am not getting any syntax error. Could you please tell me where you want me to put the ' '

Re: Networkdays function does not returning syntax error in load script

PrashantSangle — Wed, 24 Apr 2019 04:43:50 GMT

If you are using aggregation function in script then you need group by clause. Try below

temp:
load key,
min(date),
max(date),
NetWorkDays(DATE(MAX(DATE),'DD/MM/YYYY'),DATE(MIN(DATE),'DD/MM/YYYY')) as Age
resident table1
Group by key
;

Regards,

Re: Networkdays function does not returning syntax error in load script

ashis — Wed, 24 Apr 2019 05:34:43 GMT

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)

Re: Networkdays function does not returning syntax error in load script

jonathandienst — Wed, 24 Apr 2019 05:38:53 GMT

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;

Re: Networkdays function does not returning syntax error in load script

PrashantSangle — Wed, 24 Apr 2019 05:47:18 GMT

Yes it is working as expected. see in help menu.
You can also try Interval() to calculate days difference between start date and end date

Re: Networkdays function does not returning syntax error in load script

Vegar — Wed, 24 Apr 2019 06:10:03 GMT

@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.

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunctions/networkdays.htm

Re: Networkdays function does not returning syntax error in load script

ashis — Wed, 24 Apr 2019 06:23:16 GMT

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 .

Re: Networkdays function does not returning syntax error in load script

ashis — Wed, 24 Apr 2019 06:42:41 GMT

Hi Vegar,

Thank you for your input. It makes sense.

Re: Networkdays function does not returning syntax error in load script

Vegar — Wed, 24 Apr 2019 07:07:35 GMT

Regarding your interval question regarding Interval(Date(LASTDATE,'DD/MM/YYYY')
- Date(STARTDATE,'DD/MM/YYYY'),'D')

Date(LASTDATE,'DD/MM/YYYY') and Date(LASTDATE,'DD/MM/YYYY') should be
thought on as a number.
If both LASTDATE and STARDATE are 24/04/2019 then each of your Date()
expression equals to 43579.

Giving you the intervalexpression below:

Interval(43579 - 43579 ,'D') = Interval(0 ,'D') = 0