Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
ashissau
Contributor III

Networkdays function does not returning syntax error in load script

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

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;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

10 Replies
Highlighted
YoussefBelloum
Esteemed Contributor

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

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

Highlighted
ashissau
Contributor III

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

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

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

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,
Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
ashissau
Contributor III

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

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)

 

Highlighted
MVP
MVP

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

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;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Highlighted

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

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
Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Partner
Partner

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


@ashissau 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/DateAndTim...

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
ashissau
Contributor III

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

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 .

Highlighted
ashissau
Contributor III

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

Hi Vegar,

Thank you for your input. It makes sense.