Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashis
Creator III
Creator 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
jonathandienst
Partner - Champion III
Partner - Champion III

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
YoussefBelloum
Champion
Champion

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

ashis
Creator III
Creator III
Author

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

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
ashis
Creator III
Creator III
Author

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)

 

jonathandienst
Partner - Champion III
Partner - Champion III

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
PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Vegar
MVP
MVP


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

ashis
Creator III
Creator III
Author

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 .

ashis
Creator III
Creator III
Author

Hi Vegar,

Thank you for your input. It makes sense.