Evaluation logical expression and fetch matching records - Matrix

I have a question for you. In your example data, looking at RSK-1, there are two tasks associated to that risk and by definition three unique users (JAY, SONU and BIN). Yet, in you example result sheet you are only showing JAY. Why?

A second question relates to the fact that JAY is involved in both tasks associated to RSK-1, and the relation JAY and task TASK-ZZ2 occurs twice on DATA-TASK-USER (which is prefixed with a space by the way - why?). So should JAY show up twice in the result sheet or should the name be unique for the RSK-1 entry?

I am sorry but I am not convinced by your answer

for example user SONU is linked to task TASK-ZZ1 on the DATA-TASK-USER tab, and as TASK-ZZ1 is linked to RSK-1 I would expect to see an entry on the result page for RSK-1 SONU.

Furthermore, the entry TASK-ZZ3 SONU for example occurs twice on the DATA-TASK-USER tab.

Sorry, but I still think something is not right

Let me try once more

DATA-RISK-LOGIC: RSK-1 has two linked tasks TASK-ZZ1 and TASK-ZZ2
DATA-TASK-USER: TASK-ZZ1 has two users JAY and SONU

so the result should be

RSK-1 JAY
RSK-1 SONU

however SONU is not shown on the result tab. Why?

Jindon,

I believe the requirement is actually more complex. For example if you look at RSK-3 the result sheet should contain

- name of user that is assigned to either (task 1, 2 or 3) AND is assigned to (task 5 or 6)

Slightly complex to code because of the AND/OR logic


At least if you want to deliver a generic solution

Hi jsimha,

I have re-written the conditions on the main logic worksheet into:
RSK-1 AND(TASK-ZZ1,TASK-ZZ2)
RSK-2 AND(OR(TASK-ZZ3,TASK-ZZ4),TASK-ZZ5)
RSK-3 AND(OR(TASK-ZZ1,TASK-ZZ2,TASK-ZZ3),OR(TASK-ZZ5,TASK-ZZ6))
RSK-4 AND(TASK-ZZ3,TASK-ZZ4)

I believe this is basically the same set of constraints as per your logic, but just written in a different manner. This format allows me to use the EVALUATE statement after replacing the TASK ID's with avlue of 1 when found and 0 when not fouond for a given user.

The task assigned to the individual users:
TASK-ZZ1 JAY
TASK-ZZ2 JAY
TASK-ZZ1 SONU
TASK-ZZ3 JAY
TASK-ZZ3 SONU
TASK-ZZ4 SONU
TASK-ZZ1 BIN
TASK-ZZ2 JAY
TASK-ZZ3 SONU
TASK-ZZ5 BIN
TASK-ZZ6 JAY
TASK-ZZ3 JAY
TASK-ZZ4 JAY
TASK-ZZ5 SONU

I have written some code that based on the above logic produces the following results:

RSK-1 JAY
RSK-3 JAY
RSK-4 JAY
RSK-2 SONU
RSK-3 SONU
RSK-4 SONU
RSK-3 BIN

Would this be the correct result?

Hi jsimha,

Please have a look at the enclosed workbook, I think it may actually be correct (and still generic)

Dear Jay,

And a revised version (more elegant code, faster, etc)

Ok, can only look at it in an hour or so. Will let you what I find

Found the problem. My solution assumes the TASD-ID to be 8 characters in length. You can see in the caculated equation that not all value substitutions have worked (vallues must be either 0 or 1). Will have to revise he code

And found another problem. I was lazy and used Replace to substitute a task id with a 1 when found. However this also changed TASK-AA11 if it only needed to change TASK-AA1, which caused unpredictable results. All should be fixed in the appended workbook

I have not much time today, so I may not respond very quickly if you find additional problems

Hi Jay,

sorry for the trouble, but I thought of another potential problem that might occur in the future so I have changed the code again. This should be it

Hi Jay,

Assuming your translation of Q* to Task is correct, I think your equation is correct. I do not think the brackets around the OR groups are needed though.

So equation could be:
AND(OR(TASK-AA8,TASK-AA9,TASK-AA10,TASK-AA11),OR(TASK-AA12,TASK-AA13),OR(TASK-AA14,TASK-AA15,TASK-AA16))

I can't figure out whether you are experiencing errors or not. Do you want me to look at something for, or increase error tracking/reporting in the solution?

Jay, I did think about that earlier, but if it were easy I would not have asked for a different method of writing the equations to support the evaluate statement, but would have solved it in my code. I will have a go at it tomorrow or on Monday, but don't assume I can do it.

Almost have the automatic transformation working, around 80% completed :-)

Can you please check '(Qa OR Qb OR Qc) OR (Qd OR Qe OR Qf OR Qg) AND (Qh OR Qi)' on row 333 in worksheet DATA-RISK-ORIGINAL. It has and AND and OR at the same level

Jay,

automatic transformation is now working. See worksheet DATA-RISK-ORIGINAL for the results after running routine pub_sub_BuildEquation().

Some problems. Row 333 is still a problem and for certain rows there is no TASK ID filled in (i.e cell B is empty).

You have already identified the next problem (your post #36). I expected all tasks to at least start with "TASK-" I have already seen that this is not true.

The transformation routine transforms the tasks in the equations to $NAME$ so they can be more easily identified during the allocate user routine, which I have thus also adjusted, to incorporate this logic. I have changed the content of DATA-RISK-LOGIC worksheet accordingly.

Please only execute further tests with this version only!

Jay,

and as customary by now, just found another bug which needed to be solved

Hi Jay,

Herewith the most recent version (V10). The coding in this workbook has been completely re-written so you can keep using your "old" manner of writing equations.

The solution uses:
- DATA-RISK-ORIGINAL as input in the way it is currently formatted in this worksheet (so including the multiple rows for a risk id with aliases etc);
- DATA-TASK-USER is still used to obtain all tasks assigned to users;
- Result-Risk-Matrix is used as output for matched users (including supporting details such as original equation, transformed equation and actual result)

The only routine that needs to be called is:
pub_sub_AllocateUsers

You can set/change the error message reporting level (currently no messages are shown) by changing the lib_flg_ReportErrors constant at module level to True. Yoi can also request for the transform result to be written to column F for every equation by setting lib_flg_ReportTransformResult to True.

I would recommend to have a look at the DATA-RISK-ORIGINAL worksheet because some Task Id's are not provided (cell B is blank) and there is this one formula on row 333which has an AND and OR operator at the same level without brackets indicating how the evaluation should be performed

I have done some basic testing and think it should work

Rgds, Olaf

PS. I have ignored the version posted by you via Dropbox as I believe the only thing that was changed was the MAIN worksheet.

Hi Jay, any updates? Have you started testing?

Dear Jay, I was happy to help. Glad it works as desired. Please mark this thread as closed and remember to click on the star icon if you are happy with my contribution. Take care.