How to get employee count for missing years entry - Qlik Community

poonam_kulwal · ‎2016-08-27

Hello All,

I have this table. If I select any year in my list box I need corresponding HRID count. My listbox is based on Entrydate column and my master calender is also link with this column.

Mainly I am having problem with,

If I select 2016 I am getting correct HRID count

but if I select 2015, I am not getting correct count. Since my Entrydate column is missing any date for 2015, I am not getting employee count for 2015 even if my employee is working in that year.

Same with all these years 2014,2013,2012,2011.

My employee DOJ is 2006. So employee is in active status for all years from DOJ till last Entrydate.

How to get employee count for missing entrydate years?

Thanks in advance.

sunny_talwar · ‎2016-08-27

You might benefit using IntervalMatch with Master Calendar to get this done.

vinieme12 · ‎2016-08-27

I think you should be connecting master calendar WITH DOJ.

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

effinty2112 · ‎2016-08-27

Hi Poonam,

Sunny is right here. Because you're trying to associate distinct years in a calendar with a date range in another table you need to use the IntervalMatch function.

Calendar:

LOAD * INLINE [

Year

2006

2007

2008

2009

2010

2011

2012

2013

2014

2015

2016

];

Data:

Load

HRID,

Year(DOJ) as FromYear,

Year(Max(Entrydate)) as ToYear

Group by HRID, DOJ;

LOAD

HRID,

DOJ,

Entrydate

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

IntervalMatch(Year) LOAD FromYear, ToYear Resident Data;

IntervalMatch will create a table with the fields Year, FromYear and ToYear and each record in that table will be a combination of the values of those fields that allow QlikView to make the associations you're looking for.

Year	FromYear	ToYear
2006	2006	2016
2007	2006	2016
2008	2006	2016
2009	2006	2016
2010	2006	2016
2011	2006	2016
2012	2006	2016
2013	2006	2016
2014	2006	2016
2015	2006	2016
2016	2006	2016
2007	2007	2016
2008	2007	2016
2009	2007	2016
2010	2007	2016
2011	2007	2016
2012	2007	2016
2013	2007	2016
2014	2007	2016
2015	2007	2016
2016	2007	2016

You'll get a synthetic key in your data model now but in this case it's not a problem at all and there's no need to try to get rid of it.

If you select the Year 2006 in your calendar QV associates that year with these From and To years

Year	FromYear	ToYear
2006	2006	2016

So HRID = 1 is the only value of that field associated with the year.

If you select the Year 2007 QV makes this association:

Year	FromYear	ToYear
2007	2006	2016
2007	2007	2016

Now both HRIDs 1 and 2 are associated through the intervalmatch table to the year 2007.

Now you can produce this straight table.

Year	Count(HRID)
2006	1
2007	2
2008	2
2009	2
2010	2
2011	2
2012	2
2013	2
2014	2
2015	2
2016	2

Kind regards

Andrew

MarcoWedel · ‎2016-08-27

Hi,

maybe one solution could be:

tabTestData:
LOAD *,
    DayName(DOJ+Rand()*(Today()-DOJ)) as Entrydate,
    IterNo() as TransactionID
While  Rand() < 0.8 or IterNo()=1;
LOAD *;
LOAD RecNo() as HRID,
    DayName(Today()-3000*Rand()) as DOJ,
    'Active' as Status
AutoGenerate 200;

tabLink:
LOAD HRID,
    DayName(DOJ+IterNo()-1) as Date
While DOJ+IterNo()-1 <= MaxDate;
LOAD HRID,
    DOJ,
    Max(Entrydate) as MaxDate
Resident tabTestData
Group By HRID, DOJ;

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Date) as MinDate, 
    Max(Date) as MaxDate 
Resident tabLink;

hope this helps

regards

Marco

poonam_kulwal · ‎2016-08-27

Hello Sunny, Vineet,Andrew, Marco

Thanks for your suggestions.

Andrew, I tried your solution it’s working. Thanks.

But what if I have to connect my master calendar with this Year field. Here you are generating Year field from IntervalMatch function and already you are getting synthetic key.

In my app, I am taking Year, Quarter, Month fields from master calendar and I am generating Master calendar from my Entrydate field. Whatever is min Entrydate and max Entrydate (Not depending on DOJ). It is not necessary that all employee have Entrydate but employee is still working from his respective DOJ till date if status is Active for all missing Entrydate years. I have 40,000 records in my excel.

poonam_kulwal · ‎2016-08-27

Hello Marco,

I have recently started working on qlikview 5 months back.

I am having difficulty in understating ur code. Actually TransactionID is not autogenerated. It is entered by HR at the time of making any entry for Entrydate. It can be any number not necessary in an order. Same with HRD, it is not necessary I have all HRID entry for all Entrydates. But HRID is active since DOJ till date.

Will u plz explain me your code?

Thanks in advance.

Regards,

Poonam

effinty2112 · ‎2016-08-28

Hi Poonam,

In the script I included a table called calendar. This was just to take the place of the calendar in your data model. The rest of the script will work with your calendar if its year field is called Year. If it's called something different then amend the field name in the IntervalMatch brackets.

You say that

It is not necessary that all employee have Entrydate but employee is still working from his respective DOJ till date if status is Active for all missing Entrydate years.

If that is so you'll need to come up with a different way of determining the ToYear value for each HRID if some HRID do not have Entrydates. From the information you've given it's not possible for me to suggest how you would do this. Is there another date in your data model that would indicate activity? What is the reason for the status column? Does it tell something about the HRID? If so why is it in a Transaction level table? How do you know a Status is Active for a missing Entry date year?

Lastly - a synthetic key created by a correctly used IntervalMatch function is not a problem - it's just the correct link table that we need. It does not indicate a problem in the data model.

Regards

Andrew

MarcoWedel · ‎2016-08-29

Hi,

the tabTestData load only generates test data, so you wouldn't have to use Autogenerate, instead you would load your actual data.

tabCalendar is one example of a calendar script.

The relevant part is the tabLink load that links your data table with the calendar using the HRID field and one corresponding Date value for all dates between DOJ and Max(Entrydate).

Instead of DOJ you could as well use Min(Entrydate) to get all dates within the interval defined by Min and Max Entrydates.

hope this helps

regards

Marco