Matching fileds with 'or' and 'and' conditions

[#1]

Hello, I have a table where I have employees with their Job Titles and Trainings (Training Taken IDs) that those employees have taken.

Now I want to show if employees have taken mandatory (for their positions) trainings.

The twist is that one Mandatory Training can consist of a few Training codes (‘and’ condition) or it can be sufficient to take one of few Trainings (‘or’ condition). So, in my example ‘Cleaner Training 2’ training is done when employee has taken training 5000 or both 4000 and 6000 IDs (5000or(4000and6000)).

I created a table with those possible trainings. For ‘or’ conditions I have used separate records. ‘And’ condition is in one record in one field separated by semicolon (ex. 4000;6000).

The problem is hard to describe but easy when you look at the simplified application which I am attaching.

I created a straight table ‘Mandatory Trainings’ in the application where I want to list all the employees with mandatory trainings and show if the mandatory training is done or not.

In the example I am attaching the ‘Mandatory Trainings’ table last column ‘Mandatory Training done?’ should have all ‘Yes’ as in every case the condition is met.

Any help will be appreciated.

Regards,

Mat

[sunny_talwar]

May be this:

MaxString(If(Aggr(Index(Concat([Training Taken ID], ';'),[Mandatory Training Code ID]), [%Personnel Number], Name, [Mandatory Training], [Mandatory Training Code ID]), 'Yes', 'No'))

[#3]

Hello Sunny,

The formula you are proposing works only if entire string of mandatory training codes can be found in concatenated ‘Training Taken IDs’. So you cannot have any other ‘Training Taken ID’ in between which happens in real application.

I have adjusted the attached application to account for such case.

In line 1 for ‘Painter Training 1’ you should have ‘Yes’ as both 1000 and 2001 trainings are done.

Of course I do not mind if the data model needs to be changed. I just put mandatory trainings separated by semicolon as I did not have other idea.

Thanks,

Mateusz

[sunny_talwar]

I would bring the data a little differently instead of bringing it as a semi-colon separated list. Can you try something like this (I had to use partial reload, but you won't need the Add Only here)

MTC:

Add Only LOAD [Mandatory Training Code ID],

  SubField([Mandatory Training Code ID], ';') as MT

Resident [Mandatory Training Codes];

Expression:

MaxString(If(Aggr(Index(Concat([Training Taken ID], ';'), MT), [%Personnel Number], Name, [Mandatory Training], MT), 'Yes', 'No'))

[sunny_talwar]

Actually the below might not still work. Let me explore a little more.

[sunny_talwar]

Would you be able to add an example where its a no because only one of the two codes are taken(for example 4000 taken and 6000 not taken and nothing else is taken so that its a no)

[#7]

Hello Sunny,

I have added two new mandatory trainings where condition is not met.

Thank you,

Mat

[#8]

Hi,

Iam Using the personal Addition, so I cannot able to open the qvw file. Could you please let me know what is the output you need so that I will try and let you know.

Thanks,
Sreeman

[swuehl]

mat_analyst,

maybe like attached?

It's a little bit ugly code with nested advanced aggregation to check the different conditions.

I think there should be a better solution, maybe by creating a different model.

Since the result should not be depending on user selections (or could it?), couldn't you do the check within the script part?

[sunny_talwar]

Not sure what you proposed Stefan, but came up with my way of doing this also since I have been doing this for a while and didn't want to stop just because you came up with an answer

Let me know if this is not going to work under certain circumstances or if we can improve this or if we are doing the same thing

MaxString(Aggr(If(Sum(TOTAL <[Mandatory Training Code ID]> If(MT = [Training Taken ID], 1)) = Count(TOTAL <[Mandatory Training Code ID]> MT), 'Yes', 'No'),

[%Personnel Number], [Mandatory Training], [Mandatory Training Code ID], [Training Taken ID]))