topic Re: Canonical Calendar not working , the association is not working in App Development

Canonical Calendar not working , the association is not working

Saif1 — Tue, 01 Nov 2022 07:08:40 GMT

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.

Re: Canonical Calendar not working , the association is not working

robert99 — Tue, 01 Nov 2022 07:49:36 GMT

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

Re: Canonical Calendar not working , the association is not working

Saif1 — Tue, 01 Nov 2022 07:33:58 GMT

I used Person_ID for both the employee table and the training table to link to the canonical calendar.

Re: Canonical Calendar not working , the association is not working

robert99 — Tue, 01 Nov 2022 07:47:04 GMT

That won't work. See my amended post above

Re: Canonical Calendar not working , the association is not working

Saif1 — Tue, 01 Nov 2022 08:02:11 GMT

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 ?

Re: Canonical Calendar not working , the association is not working

robert99 — Tue, 01 Nov 2022 20:23:00 GMT

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.

Re: Canonical Calendar not working , the association is not working

Saif1 — Wed, 02 Nov 2022 07:02:48 GMT

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.

training table (person_id) ASSOCIATED Employee Table (person_ID)(Comp_KEY) ASSOCIATED Canonical Calendar (Comp_KEY).

Re: Canonical Calendar not working , the association is not working

robert99 — Wed, 02 Nov 2022 07:22:10 GMT

So you can't join the 2 tables by personid. As it's a many to many join. The answer is fairly simple but will let you work it out. Good luck.

Re: Canonical Calendar not working , the association is not working

Saif1 — Wed, 02 Nov 2022 07:30:48 GMT

Thank you so much for the help , really appreciate it.