Employee Count

[krishna20]

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.

[krishna20]

Hi Friends,

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

[isorinrusu]

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.

[DavidFoster1]

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.

[krishna20]

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.

[avinashelite]

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

[DavidFoster1]

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

David Foster

Senior QlikView consultant

[vikasmahajan]

Go as per Manish Solution it worked for me

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

HTH

Vikas

[krishna20]

Hi Foster,

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

[krishna20]

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;