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: 
Not applicable

Using NetWorkDays() in the data load editor

Hi there.

Is it possible to use the networkdays() function in the load script to generate a new internal field from two loaded dates?

I've been trying something like:

issues_data:

LOAD

  Id,

    ...

    Request_date,

    End_date,

    NetWorkDays(Request_date, End_date) as Net_work_days,

    ...

FROM [...]

();


Where "Request_date" and "End_date" are the names of two columns in my table data containing dates, more or less as follows:

Noname.jpg

My current output, when using the function as stated above, are only 0 and 1, instead of what should be expected.


So I don't really know if it's even possible to use the function the way I'm trying it.


I've also seen a reply to a question similar to mine saying that it should be used: "Networkdays(date#(In_date, 'DDMMYYYY'), date#(Out_date, 'DDMMYYYY'))", but doesn't work either.


Anyway, many thanks in advance.

M.

1 Solution

Accepted Solutions
sunny_talwar

It should work. Do you have a sample file to look at?

View solution in original post

11 Replies
Not applicable
Author

Try this thread function:networkday

sunny_talwar

It should work. Do you have a sample file to look at?

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Are the dates proper date values or are they strings? If they are strings, then I strongly recommend that you convert these to date values when you load the data:

     LOAD ... 

          Date#(myStringDateValue, '... source date format ...') As Date,  

          ....

What do your dates actually look like?

And you can certainly use the function the way you are doing it.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar

This is working for me:

Table:

LOAD *,

  NetWorkDays(Date#([Request_Date], 'DD/MM/YYYY'), Date#([End_Date], 'DD/MM/YYYY')) as [Working Days],

  Num(Date#([End_Date], 'DD/MM/YYYY') - Date#([Request_Date], 'DD/MM/YYYY')) as Difference; 

LOAD * INLINE [

    Request_Date, End_Date

    17/06/2015, 17/06/2015

    17/06/2015, 18/06/2015

    17/06/2015, 19/06/2015

    18/06/2015, 18/06/2015

    18/06/2015

    18/06/2015, 25/06/2015

    19/06/2015

    23/06/2015, 26/06/2015

    24/06/2015

    24/06/2015, 30/06/2015

];

gerry_hdm
Creator II
Creator II

you define a variable with the Holiday

example    vHoliday =  '01.05.2015','02.05.2015'

give it the Format from your Region  '01.05.2015','02.05.2015' (germanregion )

then you  using 

networkdays( startDay, endday ,$(vHoliday)) as workdays

Gruß Gerold

Not applicable
Author

Thank you.

Not applicable
Author

Thank you.

Not applicable
Author

Thank you.

Not applicable
Author

Hi Sunindia.

As you said it should be working I created a new app from scratch (attached) and now it seems to work fine, so the problem might be in my original application. Maybe it's something related to what Jonathan Dienst said in his reply.

Anyway, I'll further investigate in that direction.

Thank you very much.