topic Re: Employee Count in QlikView

Employee Count

krishna20 — Tue, 11 Aug 2015 10:02:08 GMT

Hi Folks,

I'm having a Employee_Leave table consists of LEAVE_START_DATE and LEAVE_END DATE. I need to show the employee's leave count and remaining leaves. How can we show it in the dashboard? I'm planning to show with arbitrary date ranges.i.e., when the start date selected the employees on leave will be shown.

But having a doubt to implement this scenario. In the arbitrary date range only one date will be used as LEAVE_START_DATE and end date. Where i need to consider the LEAVE_END_DATE??How the two dates will be combined as one? and How can i show this scenario clearly to understand.Please anyone suggest me.Kindly find the sample app.

Re: Employee Count

krishna20 — Tue, 11 Aug 2015 10:22:04 GMT

Hi Friends,

Can anyone please suggest.It's urgent and needed..

Re: Employee Count

isorinrusu — Tue, 11 Aug 2015 10:42:40 GMT

Maybe this will help: Canonical Date

What you need is to have another table where the leave and coming back date is related to each customer. For example, you'd have employee E in Table T1 that is associated to the table T2 by the employee's ID, this table having the fields LeaveDate and LeaveEndDate, as well as a filled called CanonicalDate.

T1(EmployeeID, EmployeeName....)

T2(EmployeeID,LeaveDate,LeaveEndDate,CanonicalDate)

When you use the dates in your UI, you'd use the CanonicalDate field.

Re: Employee Count

DavidFoster1 — Tue, 11 Aug 2015 10:43:38 GMT

Hi krishna20

You sample app contains no data, so it was difficult to understand what you are trying to achieve.

In this kind of situation where you want to query your data using arbitrary dates that may fall inside the date range of a leave record I would recommend you use a WHILE LOOP in your load statement to explode your data into single day/date records. That way at any date you can calculate how many day's leave each employee has taken and how many they have left.

If you can provide an example with data I could add an example WHILE loop.

Re: Employee Count

krishna20 — Tue, 11 Aug 2015 10:57:43 GMT

Hi David / Sorin,

Thank you for valuable reply. Data is there.For some dates there is no values.Please check it by selecting the LEAVE_START_YEAR and LEAVE_END_YEAR.Please, guide me how to show the information clearly.

Re: Employee Count

avinashelite — Tue, 11 Aug 2015 11:06:45 GMT

hi ,

I this case you need to create a link table which combines both the date and finally link the combine date with calendar

try like this :

EG:

Emp_Details:

LOAD EMPID,

LEAVE_START_DATE ,

LEAVE_END DATE

....

from table

Link_Table:

load EMPID,

LEAVE_START_DATE as Date_Key

resident

Emp_Details;

Load EMPID,

LEAVE_END DATE as Date_Key

resident

Emp_Details;

Calendar:

LOAD Date as Date_Key,

.....

NOTE:* Keep all the date format same.

*In the calendar using this new date i.e Date_key and count the leaves accordingly

Re: Employee Count

DavidFoster1 — Tue, 11 Aug 2015 12:27:40 GMT

When I downloaded the .qvw there was a load script but no data and no charts/tables

David Foster

Senior QlikView consultant

Re: Employee Count

vikasmahajan — Tue, 11 Aug 2015 12:30:53 GMT

Go as per Manish Solution it worked for me

https://community.qlik.com/thread/153749

HTH

Vikas

Re: Employee Count

krishna20 — Tue, 11 Aug 2015 12:35:54 GMT

Hi Foster,

Please find the attachment and guide me with a view of objects ..

Re: Employee Count

krishna20 — Tue, 11 Aug 2015 13:24:43 GMT

Hi,

I'm confused to implement this linked table.Because the both date fields are coming from same table.whether i need to concatenate them or not and in the Master calendar whether i need to use START_DATE or END_DATE??Please suggest me as per the below code.

PER_ABSENCE_ATTENDANCES:

LOAD ABSENCE_ATTENDANCE_ID,

ABSENCE_ATTENDANCE_TYPE_ID,

ABS_ATTENDANCE_REASON_ID,

ApplyMap('ABSENCE_REASON',ABS_ATTENDANCE_REASON_ID,NULL())as [LEAVE REASON],

ApplyMap('LEAVE_TYPES',ABSENCE_ATTENDANCE_TYPE_ID,NULL())as [LEAVE TYPE],

PERSON_ID,

ABSENCE_DAYS,

ABSENCE_HOURS,

DATE_END ,

DATE_START,

TIME_END,

TIME_START,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

MATERNITY_ID,

DATE( SICKNESS_START_DATE,'DD/MM/YYYY') as [SICKNESS START DATE],

DATE( SICKNESS_END_DATE,'DD/MM/YYYY') as [SICKNESS END DATE],

APPROVAL_STATUS

FROM

(qvd);

Link_Table:

load PERSON_ID,

DATE_START as Date_Key

resident

PER_ABSENCE_ATTENDANCES;

LOAD PERSON_ID,

DATE_END as Date_Key

resident

PER_ABSENCE_ATTENDANCES;

Calendar:

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(DATE_START) as minDate,

max(DATE_START) as maxDate

Resident PER_ABSENCE_ATTENDANCES;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Employee_Joining_MasterCalendar:

Load

TempDate AS DATE_START,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: Employee Count

avinashelite — Tue, 11 Aug 2015 13:35:16 GMT

just add one more column in the calendar as below, rest of the code look correct

TempDate AS Date_Key,

note : keep the date formats same i.e calendar and start and end date format same

like date(START_DATE,'DD-MM-YYYY') as Date_Key

date(TempDate ,'DD-MM-YYYY') as Date_Key

date(END_DATE,'DD-MM-YYYY') as Date_Key

Re: Employee Count

krishna20 — Wed, 12 Aug 2015 04:56:35 GMT

Hi ,

Greetings Avinash,

In the Temp Table which Date i need to take here. Whether DATE_START or DATE_END?? or i need to Take Date_Key here???Please suggest me..

Calendar:

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(DATE_START) as minDate,

max(DATE_START) as maxDate

Resident PER_ABSENCE_ATTENDANCES;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Re: Employee Count

avinashelite — Wed, 12 Aug 2015 07:00:39 GMT

no , design the calendar according to your need, something like 1990 to 2030

if you what to make the calendar according to the data i.e START or END Date then you need to do a group by and fetch the max from end date and min from start date

Re: Employee Count

krishna20 — Wed, 12 Aug 2015 07:17:17 GMT

Hi Avinash,

I'm very new to Link table concept.I need to show the data according to Start_Date and End_Date. Can you please edit in the sample file and suggest me. It helps me to learn this concept as well as not to miss anything in the script further.

Re: Employee Count

Anonymous — Thu, 13 Aug 2015 22:01:24 GMT

Hello Qlik Community Members,

We got a flag that were was personally identifiable information in this thread. I've just removed the attachments as it looks like you have found a solution. IF you still need to re-upload an example please be Extra Careful that it has been cleansed of any personal or confidential information.

Also- Mr. Krishna please take a moment to mark helpful and correct responses where applicable.

Best Regards, Sara

Re: Employee Count

krishna20 — Fri, 14 Aug 2015 05:01:36 GMT

Hi Sara,

I have already marked this thread as Helpful.

Regards

Krishna