Match two fields, then find Data that does not exist in one compared to the other

[ciphergod]

Hi all, facing an issue which require some advice as i have no directions to work on at the moment.

I have 3 tables, A, B and C.

Essential columns in A are: Employee ID, Employee Name, Employee Designation

Essential columns in B are: Employee ID, Employee Name, Course Employee Attended, Course Validity, Refresher Date

Essential Columns in C are: Designation, Course to attend(multiple rows of different courses to attend)

From the 3 tables, i will need to be able to do 2 things

1.  Find out which course my employees have not attended, based on what they are supposed to from their designation (from A and C), compared to what they have attended (from B)

2. Find out when the courses they have attended will expiry, and will require refresher.

The second requirement is straight forward, i was able to do it with B alone. However i am facing difficulty in how to even go about fulfilling the first requirement. Best case scenario will be that i can do both requirement in a single app. However if that is impossible, i am open to having 2 apps, one to track refresher, one to track required courses not yet attended.

Any ideas on what directions i should go about? (i.e. any specific function or script that meets my requirements?)

[lorenzoconforti]

Is this the same as what we already discussed?

https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-check-if-a-value-in-Column-A-match-Column-B/m...

[ciphergod]

Yes it is similar! but in this situation i was approached with a slight additional requirement which throws me off almost completely. Whatever we discussed previously still works (actually in use now).

The situation is that the user informed me that it only gives the output for courses that they had previously went for, and had a Date Attended column. They mentioned to me that it was unable to show courses which are required, but where the user has totally not attended in the past. (they realised this issues due to a new hire where nothing is reflected, there is no issue with current employees tracking for re-training)

Perhaps it is not an issue with the data structure, but more of how i should phrase the chart dimension/measures to display what the user needs in this case, but i am unable to think of anything at this moment, any advice?

[ciphergod]

any ideas or directions?

[lorenzoconforti · Accepted solution]

Can you build an example dashboard to clarify the requirement? Maybe build on the one I uploaded on the other thread

[ciphergod]

Hi,

I was attempting to build using my current data as closely to the one you provided previously, however i face an issue of synthetic key for the field NameCourse.

Despite the presence of synthetic key, i was able to get the result i needed using some formulas to create a master dimension as advised by one of my colleague. 

My question now would be, if it is okay for an app to operate with a synthetic key, or should i be attempting to resolve this synthetic key? Note that i did a sanity check on the data with synthetic key, it gives accurate data as what i need (at least from my random check)

[shivanisapkale]

Hello,

The app will work fine and data will be accurate even after synthetic key,but this is not a best practice so try to remove it.

Regards,

Shivani Sapkale