Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
nicouek
Creator
Creator

Table Association / Calendar

Hello All,

I have a project I am working on and got stuck, I was wondering if someone could help? Thank you

I have 2 tables, a [HR _WRKF] table ,and [HR_TRNG] table. I am trying to determine what % of employees  took the training?

Answer --> [HR_TRNG]/[HR _WRKF]...fair enough

My issue is when I apply a calendar to aggregate each count by YEAR in the filter pane, one of the table ID count changes. I think my issue may be in the calendar association but I am not sure... If there is any insight you can provide please assist and if you need more info do not hesitate, please ask.

Thank you

Labels (1)
5 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you post a screenshot of your datamodel from the Data Model Viewer?

-Rob

nicouek
Creator
Creator
Author

Hello Rob

Sorry about the delay, had to cook up a data model since can't share the actual data.

nicouek_0-1649266286071.png

Hope the image makes it... The WRKF_DATE is a common table developed by admins to kind of by pass a Master calendar creation, I don't like it but...  hope this helps. Thank you

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I don't see any links between the tables?

-Rob

vinieme12
Champion III
Champion III

You basically need to link all your tables in the model with the calendar

 

Step1) create a new field in HR_WRKF same as Emp_ID

Emp_ID as Employee_Id

Reason: We don't use key fields for aggregation, 

field Emp_ID will be used to link your TRNG and WRKF tables

 

Step2) Create a new table to consolidate Start_dt from HR_WRKF and completion_dt from HR_Training table into one table as below

LinkTable:

Load

Start_dt as JOINT_DT

,Emp_Id

,'StartDate' as DateType

Resident HR_WRKF;

Concatenate(LinkTable)

Load

Completion_DT as JOINT_DT

,Curr_emp_ID as Emp_Id

,'CompletionDate' as DateType

Resident HR_TRNG;

 

Step3) link the TRNG table to the LinkTable

Rename field Curr_emp_ID to Emp_Id;

 

Then in your charts use

count(Distinct Employee_Id)/count({<DateType={'CompletionDate'}>}Distinct Employee_Id)

 

 

 

 

 

 

 

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

Good morning to all that replied to my thread

I would like to thank you for your time and consideration. My silence shouldn't be taken as ungratefulness. I applied some of your suggestion and still didn't get the desired results, however I have been able to resolve my problem with "IntervalMatch". Intervalmatch [Effective_From_DT] and [Effective_To_DT]; I think associating these two fields helped identified each CURR_EMP_ID count . Thank you very much again... Cheers!!!