Gap analysis - how to compare actual with required training

[#1]

Hi, I would like to ask for some guidance to solve a problem I have.

To put it in simple words (real data model is more complex) I have a list of persons with their job positions and trainings they have taken.

I have also a list of mandatory trainings for each job position.

What I would like to do is create a table (for gap analysis) where I could see (by employee, job position, etc.) what mandatory trainings are missing.

Is that possible to do without changing data model – only by expressions in table?

Thanks & Regards,

Mat

[Anonymous]

As I understand, when you select a person and a job position, you get a list of training taken.  And, you want a list of training required but not taken.

I'm sure it is possible with se analysis.  How exactly is hard to tell without your application, but shouldn't be complex.

[Gysbert_Wassenaar]

Maybe. It might be possible to tell with more certainty if we actually knew your data model, or at least the relevant parts of it. Can you post a qlikview document to demonstrate the problem? This document might help with that: Preparing examples for Upload - Reduction and Data Scrambling

[#4]

Hi, I have prepared example with some random data. At the end I need to prepare such table to easily filter what employees are missing their required trainings:

Employee	Job	Mandatory Training	Mandatory training taken?
Alan Alton	Driver	Training 5	No
Alan Alton	Driver	Training 6	No
Alan Alton	Driver	Training 7	Yes
John Robinson	Painter	Training 1	No
John Robinson	Painter	Training 2	No
John Robinson	Painter	Training 3	Yes
John Robinson	Painter	Training 4	No
Paul Oxley	Painter	Training 1	No
Paul Oxley	Painter	Training 2	Yes
Paul Oxley	Painter	Training 3	No
Paul Oxley	Painter	Training 4	Yes
Thomas Owen	Driver	Training 5	Yes
Thomas Owen	Driver	Training 6	No
Thomas Owen	Driver	Training 7	No

[Anonymous · Accepted solution]

Here is the expression that works:

if(index(concat([Training Taken], '|'),[Mandatory Training]&'|'),'Yes','No')

[#6]

Hi Michael,

It works great and it is very neat solution. Thank you.

To my exemplary file I have also added expiry date and training status (expired or valid). I was able to adjust the formula to take into account the status. However I wonder if it is possible to add to my table also the expiry date for each of the mandatory trainings that was taken (no matter if expired already or valid)? I cannot add additional dimension with date as I will get my rows multiplied. I cannot find a way to filter only matching row.

Obviously you have helped and answered my original question so I will mark your answer "Correct Answer" anyway. I just wonder if it would be difficult to add this date…

Regards,

Mat

[Anonymous]

You mean that if expiration date is in the future, the training is considered taken.  If correct, here is the expression that should work:

if(index(concat(if([Expiry Date]>ReloadTime(),[Training Taken] &'|'))&'|',[Mandatory Training]&'|'),'Yes','No')

(I prefer to use Reload time() rather than today() on the front end, so the data is correct as of the reload time.  That is, the definition of today = reload time.)

[#8]

Thanks Michael, but it is not exactly what I would like.

I would like to add additional column to my table with expiry date (for each person's mandatory trainings). Of course expiration date exists only for trainings that have been taken.

The purpose behind that is application user is actually interested in seeing not only if some training was taken or not (and if training is still valid) but also when the valid training will expire (or when it expired if it is no longer valid).

Regards,

Mat

[Anonymous]

This?

=aggr(if(Index(Concat([Training Taken],'|')&'|',[Mandatory Training]&'|'),[Expiry Date]),Employee, [Mandatory Training], [Training Taken])

[#10]

Thank you Michael, that works perfectly.

Regards,

Mat