Canonical Calendar not working , the association is not working
Hello QLikers,
i have 2 tables :
1- Employee table (has the start date and end date of an employee) and departments
2- training table ( has training courses , training start date and end date)
both table are associated with (Person_ID) Key, same goes to the canonical calendar.
using other articles i have made the Canonical Calender associated with it a master calendar to get the year , month , etc
my problem is , when i am clicking any date in the calendar, it gives me all possible person_ID , becuase the link is person_ID , what i want is to get me that spesific date for all of the possible Employees that have anything related to that date.
You still join the employee table and training table by personid. As long as there is a one to many association between these tables. And then join the training table to the calendar using uniqueref
The reason you can't use PersonID is because one person might attend many different training events on different date. So it will not work. (As many dates will join to many dates).
But try what I recommend and let me know if it works.
The field that you use in the training table to link to the canonical table must be unique. That is one row has only one unique ref. It can't be personid. If required create a new field using rowno() as UniqueRef. And use UniqueRef to link to the canonical calendar
does it make sense? if i made RowNo() as UniqueRef, from every table different employee will have different RowNo() , it would be unique but the association random isn't it ?
You still join the employee table and training table by personid. As long as there is a one to many association between these tables. And then join the training table to the calendar using uniqueref
The reason you can't use PersonID is because one person might attend many different training events on different date. So it will not work. (As many dates will join to many dates).
But try what I recommend and let me know if it works.
it worked, but with only the Employee table (which has multiple dates per employee becuase of the department changes per years) i made a Composte key (person_ID with the start date ) and hooked it with the canonical calendar) it worked BUT with only the employee table hooked with the Calendar, am trying to figure out how to hook the Training table with the employee table and with the canonical Calendar without having Synth keys.
