topic Re: Variable help.. in QlikView

Variable help..

Mon, 25 Mar 2013 12:38:30 GMT

Hi everyone,

I need help with a variable...

I have attached a sample.

here is what i need to achieve.

1. I need a variable which contains Public holiday dates

2. i need a variable that contains employee sickness / holiday / training dates.

3. then i need to add these 2 varibables to a variable that calculates 'Days in Range' which is then used to work out averages.

Ok, i have completed point 1 (public holidays variable)

i dont know how to create the 2nd variable. i need it to contain the following 'Diary Event Type codes'..

01, (holiday)

02, (Sickness)

03, (Other)

13, (Field Day ISS) - Training.

I have a variable 'vDaysinRange' which works out the working day range.

So i want to add in the vBankHolidays and the new variable from point 2 (both to be excluded) into 'vDaysinRange'

can anyone help please?

Re: Variable help..

Gysbert_Wassenaar — Mon, 25 Mar 2013 13:10:04 GMT

PublicHolidays:

LOAD

// concat(chr(39)&date(HolidayDate)&chr(39),',') AS PublicHol

concat(distinct num(HolidayDate),',') AS PublicHol

FROM comm75724.xls

(biff, embedded labels, table is [BankHol$]);

//create variable containing public holiday dates

LET vBankHolidays = peek('PublicHol',0,'PublicHolidays');

AbsentDays:

load concat(distinct AbsentDay, ',') as AbsentDays;

load DiaryEventDateFrom + iterno() -1 as AbsentDay

while DiaryEventDateFrom + iterno() -1 <= DiaryEventDateTo;

Load DiaryEventDateFrom, DiaryEventDateTo from

comm75724.xls

(biff, embedded labels, table is [DiaryEvent$])

where match(DiaryEventTypeCode,'01','02','03','13');

LET vAbsentDays = peek('AbsentDays');

SET vAbsentDays2 = $(vBankHolidays),$(vAbsentDays);

SET vDaysinRange = networkdays(vMinDate,vMaxDate,$(vAbsentDays2));

edit: highlighted the where clause for DiaryEventTypeCode)

edit 2: highlighted the changes to the PublicHolidays load

Re: Variable help..

Mon, 25 Mar 2013 13:17:12 GMT

Hi Gysbert,

thanks for your reply.

In your example, the vAbsentDays only need to include dates where the

'Diary Event Type code' is either 01,02,03,13. that is where i am stuck at.

Is it possible for you to put it into my sample? its easier for me to understand that way if its not too much trouble

Re: Variable help..

Mon, 25 Mar 2013 14:13:12 GMT

hi Gysbert , yes i see that now, sorry, i completely over looked it!

i cant seem to get the DATE function to work on the new field in your example. the vAbsent Days variable is displaying a mixture of formats.

Can you point me in the right direction to format this field please?

Thanks

Re: Variable help..

Mon, 25 Mar 2013 15:11:44 GMT

Hi again..

This doesnt seem to be working...

This month for example has 21 working days in it (thats if no employees have no time off) but looking at the new sample i have attached, every employee has 21 days against them.

employee code 201 has had 4 days off sick this month so the daysin range for them should be 17 not 21.

Is the variable EXCLUDING the dates in the vAbsentDays2 variable?

thanks

Re: Variable help..

Gysbert_Wassenaar — Mon, 25 Mar 2013 15:33:32 GMT

Ah, you need it per employee. Different approach then with a very simple flag field. See attached qvw.

edit: SET vDaysinRange = networkdays(vMinDate,vMaxDate,$(vBankHolidays)) - count({<DiaryEventTypeCode={'01','02','03','13'}>} total <DiaryEventEmployeeCode> DiaryEventEmployeeCode);

Re: Variable help..

Mon, 25 Mar 2013 16:07:00 GMT

Thats great, sorry i should have said that in the beginning..

One other question though...

Is there a way to get the solution in a variable?

The application this needs to go in is already in use and uses the variable 'DaysInRange' which currently only works out working days. They want it changed to deduct Bank Holidays and Absence too, so i would like to just update the variable otherwise it would mean changing the set analysis in loads of expressions across the whole app..

Is this possible?

Thanks for your help..