If(match... and match...) not working

[ciphergod]

Hi all,

Facing some issues trying to match some information, unable to share any sample data so i can only try my best to explain my current situation.

Fields involved - Name, Training Attended, Appointment

Basically for each appointment, there is a list of courses they need to attend. Each name is tagged to one or more appointment. 

On the side, there is a subset of courses from the full list tagged to appointment that are required before the person can officially start work on the ground(lets call it OJT trainings). 

I am trying to create a master dimension that will match based on roles, and the match to see if they have attended all the OJT trainings required, before outputting whether the OJT is ongoing or completed.

Here's what i have at the moment with some arbitrary values, note that are are certain appointments that have a list of required courses, plus a separate list of courses where they have to finish at least 1 to complete their OJT, e.g. driver as seen below.

=

if(match(Appt,'Driver'),

if(
(
(
(match([Training Attended],'Course A'))
and (match([Training Attended],'Course B'))
and (match([Training Attended],'Course C'))
and (match([Training Attended],'Course D'))
and (match([Training Attended],'Course E'))
and (match([Training Attended],'Course F'))
and (match([Training Attended],'Course G'))
and (match([Training Attended],'Course H'))
and (match([Training Attended],'Course I'))
and (match([Training Attended],'Course J'))
and (match([Training Attended],'Course K'))
and (match([Training Attended],'Course L'))
and (match([Training Attended],'Course M'))
)
and
(
(match([Training Attended],'Course 1'))
or(match([Training Attended],'Course 2'))
or(match([Training Attended],'Course 3'))
or(match([Training Attended],'Course 4'))
or(match([Training Attended],'Course 5'))
or(match([Training Attended],'Course 6'))
or(match([Training Attended],'Course 7'))
or(match([Training Attended],'Course 8'))
)
)
,'Completed','Ongoing'),

if(match(Appt,'Technician'),

if(
(
(match([Training Attended],'Course A'))
and (match([Training Attended],'Course B'))
and (match([Training Attended],'Course C'))
and (match([Training Attended],'Course D'))
and (match([Training Attended],'Course E'))
and (match([Training Attended],'Course F'))
and (match([Training Attended],'Course G'))
and (match([Training Attended],'Course H'))
and (match([Training Attended],'Course P'))
and (match([Training Attended],'Course Q'))
and (match([Training Attended],'Course R'))
and (match([Training Attended],'Course S'))
)
,'Completed','Ongoing'),

if(match(Appt,'Operator'),

if(
(
(match([Training Attended],'Course A'))
and (match([Training Attended],'Course B'))
and (match([Training Attended],'Course C'))
and (match([Training Attended],'Course D'))
and (match([Training Attended],'Course E'))
and (match([Training Attended],'Course F'))
and (match([Training Attended],'Course G'))
and (match([Training Attended],'Course H'))
and (match([Training Attended],'Course I'))
and (match([Training Attended],'Course J'))
and (match([Training Attended],'Course K'))
and (match([Training Attended],'Course L'))
and (match([Training Attended],'Course M'))
and (match([Training Attended],'Course N'))
and (match([Training Attended],'Course O'))
)
,'Completed','Ongoing'),

'Not Applicable')))

There seem to be something wrong that made it unable to give any output. Any idea what is wrong? Or maybe any idea of what could be a better way to express what i am trying to do? This master dimension will be place in a table chart beside Name to know the status of each person.

Thank in advance.

[ciphergod]

Apart from the section where i cant figure how to appropriately handle the list of training where they need to complete at least 1, along with the rest of the 'compulsory' training. I think the main issue would be this master dimension giving two results of Completed and Ongoing. My current formula apparently does not behave in the way that it gives a status of either this or that. Instead due to the existence of other training not included in OJT, it gives the Ongoing Status, and it gives the Completed Status even if the person only completes 1 of the course.

Any ideas?

[ciphergod]

any help please!(:

[tm_burgers]

While I'm not 100% sure what you are trying to achieve. 

My first instinct would be to create the course tracts as their own dimensions, and then assign the Courses within those tracts as a numerical value - then you could check progress as a sum. 

for example and using your terminology:

CoursesA-Z = CourseTract1 

Course A = 1

Course B = 1 , etc. 

Then you can do and if statement like 

if(match(Appt,'Driver'), if(sum_of_courses_attended = max,'Completed','Ongoing'), ....

in this manner you would also be able to show a completion % 

etc for the other Appt's

[ciphergod]

thanks for your suggestion, really like the part that allows the indication of % completion.

I ended up creating a separate app using the data, and using Generic load, made all the courses into dimensions, similar to what you suggested (just that it would be more difficult to create it one by one as per your suggestions since i have more roles than what listed and each roles have many different courses req)

It worked as i would liked it to, other than that it could not be integrated into the original app i made with other functions and visualisations.