topic Re: How can i make this work?? in QlikView

How can i make this work??

Sun, 10 Feb 2013 20:48:02 GMT

In my example i have a inline table with from date -> end data

My problem is that i have different Holiday data for different records.

the holiday data is stored as string in the record.

Now i want the networkdays function use this field (Holiday), but it is ignoring these values! See (30-04-2012)

How can i change the script in such a way that the Holiday fields is correct used in my (not mine ) function.

Hope someone can help me!!!

Re: How can i make this work??

Sun, 10 Feb 2013 22:41:52 GMT

You are using Dollar Sign Expansion (DSE) on the field Holiday from the preceding Load. However, there is nothing really to expand. Further, I don't think you can call NetWorkDays() and as the holiday arguemnt(s) pass a single string that is comma delimited. The NetWorkDays() function can accept a variable number of arguments for the holidays, but you cannot pass them all as a delimited list in a single argument list this.

EDIT: NetWorkDays() apparently does accept a comma delimited list. See additional responses below for an easier solution (thanks to swuehl).

You rely on NetWorkDays() in a few places in your Interval() expression, so what you could do is use another preceding load to calculate that (just so you only have to do it once), and use an approach whereby you determine the number of holidays you have for the record, then call the NetWorkDays with multiple arguments as needed. You could use substringcount() to look for commas in the Holiday field to determine how many you have. Then use subfield() to parse them out. Wrap that in an inline IF function (a rather long one, depending on the maximum number of holidays you need to support).

Somethings like this:

if(len(trim([Holiday]))>0,
    pick(substringcount([Holiday],',')+1,
        Networkdays(Begin+1,End-1,[Holiday]),
        Networkdays(Begin+1,End-1,subfield([Holiday],',',1),subfield([Holiday],',',2)),
        Networkdays(Begin+1,End-1,subfield([Holiday],',',1),subfield([Holiday],',',2),subfield([Holiday],',',3)),
        Networkdays(Begin+1,End-1,subfield([Holiday],',',1),subfield([Holiday],',',2),subfield([Holiday],',',3),subfield([Holiday],',',4))
)
,Networkdays(Begin+1,End-1)) As NWD_Value,

That expresion covers up to 4 holidays, but could be expanded as needed.

Message was edited by: Donald Hutchins (clarification, see "EDIT")

Re: How can i make this work??

Mon, 11 Feb 2013 08:14:49 GMT

Networkdays accepts comma separated holidays

So there has to be à simple solution

Thnx for your answer

Re: How can i make this work??

swuehl — Mon, 11 Feb 2013 09:10:27 GMT

Jochem,

as Donald pointed out, your dollar sign expansion returns nothing. I think you'll need to create a loop over all IDs, creating a holiday variable per iteration and pass it to the networkdays function (if you want to do this in the script).

See attached.

Stefan