topic networkdays from field in QlikView

networkdays from field

Tue, 11 Dec 2012 13:49:22 GMT

Dear all,

I'm trying to do networkdays from a Field but it's not working correctly.

I have a calendar with near 100 departments and each one has his own holiday calendar.

I'm creating a new Table with two fields,

first one is the department and second one is the concat of the holiday days.

LET Counter = FieldValueCount('Department');

For i=0 to $(Counter)-1

Let Dep1 = peek('Department', $(i), 'Agency');

Holidays_Temp:

LOAD Distinct Date (Days,'DD/MM/YYYY') AS Holiday_date

FROM File.qvd (qvd)

WHERE Department='$(Dep1)';

Holidays:

LOAD

$(Dep1) AS Department,

concat(chr(39) & date(Holiday_date,'DD/MM/YYYY') & chr(39),',') AS Holidays_End

RESIDENT Holidays_Temp;

Drop Table HolyDays_Temp;

After that I'm doing a left join to another table, but function

networkdays(Init_Date,End_Date,Holidays_End) - 1 doen't work, but If I put Holydays_End on a variable and I do the same formula on textbox it works.

How can I use a field as Holidays without use a variable?

Best regards

Re: networkdays from field

Wed, 23 Jan 2013 11:15:51 GMT

Any ideas?

Re: networkdays from field

upendrarajeshir — Mon, 10 Feb 2014 11:26:20 GMT

Hello,

Did you find any solution?? If yes then pls share because I have same problem and looking for help.

Thanks in advance

Re: networkdays from field

tresB — Mon, 10 Feb 2014 11:33:12 GMT

Upendra, I guess it would be a better idea that you create a sample qvw and post in a new thread describing your issue.

Re: networkdays from field

jonathandienst — Mon, 10 Feb 2014 13:05:15 GMT

This should work for Holidays in a field. Assuming a table containing the Department and Holiday dates

T_Holiday:

LOAD Department,

Date(Date#(Holiday)) As Holiday //interpret as a date and format with your default date format

FROM Holidays.txt (txt,...)

HolidayLists:

LOAD Department,

chr(39) & concat(Holidays, chr(39) & ',' & chr(39)) & chr(39) As DeptHolidays

FROM T_Holiday

GROUP BY Department

;

DROP Table T_Holiday;

You can use this is a table with Department as a dimension:

=NetWorkDays(Start, End, $(=DeptHolidays))

I haven't tested this, so they may be typos and other issues, but give it a try.

HTH

Jonathan