Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You might benefit using IntervalMatch with Master Calendar to get this done.
I think you should be connecting master calendar WITH DOJ.
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
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
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.
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
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
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