Identifying Users With Toxic Roles - Use Case

I have 2 tables

First Table: If you notice the Rows are repeated for some of the IDs. If an ID is associated to multiple ROLES, each ROLE will have a separate entry with the ID

ID	Role
100	BUSVF
100	BUSMK
100	BUSVWR
101	BUSMK
102	BUSVWR
103	MIGVF
103	MIGMK

Second Table: This is the Toxic Role Rules Table

Valid_Role	Toxic_Role
BUSVF	BUSMK
MIGVF	MIGMK

How to read this rules table --

An ID having BUSVF should not also have BUSMK

An ID having MIGVF should not also have MIGMK

Therefore, I would need to compare the second table with the first table and throw exceptions where the Toxic Rules Table match

For example consider ID 100 in the first table.

ID 100 has roles BUSVF, BUSMK, BUSVWR

The toxic rules table states that an ID having BUSVF should not also have BUSMK. Therefore, ID 100 is an exception.

How do I go about achieving this ?

Workflow

Input

Output

Tips and Tricks

Transformation

Preparation

Accepted answers

messi007

@surajmthomas,

Please see below

attached the workflow,

Hope this helps!

Regards,

Test_20210701.yxmd

AngelosPachis

@surajmthomas or you can tweak it even further to the role granularity, so

100 - BUSMK

and

103 - MIGMK

are exempted

example_v2.yxmd

All comments

messi007

@surajmthomas,

Please see below

attached the workflow,

Hope this helps!

Regards,

Test_20210701.yxmd

AngelosPachis

Hi @surajmthomas ,

Not sure this is what you are looking for but do give it a try. What it does is it checks if a Toxic ID is found in your original table and flags it out. So based on the logic I assume you described, the only ID which is not toxic and should not be exempted is 102

Hope that helps,

Angelos

example.yxmd

surajmthomas

@AngelosPachis Thank you Angelo for your time. Much Appreciated.I have tried this. This is partially correct, but not quite what I am looking to achieve. The values in the Toxic Column are not always Toxic. What I mean to say is

The user can have a role BUSMK. Therefore, the user 101 associated with BUSMK is valid. 102 associated with BUSVWR is also valid

What I am trying to achieve here is according to the Valid Toxic Relationship table is

A user having BUSVF cannot also have BUSMK as a role. Therefore, if you look at ID 100, it has BUSVF, BUSMK and BUSVWR. Here, 100 having BUSVF, BUSVWR is ok. But because of the Valid Toxic Relationship table, ID 100 having BUSMK is invalid.

Therefore my exception should be

100 - BUSMK

surajmthomas

@messi007Thank you for your time and help. I am trying this out. Will revert.

AngelosPachis

@surajmthomas or you can tweak it even further to the role granularity, so

100 - BUSMK

and

103 - MIGMK

are exempted

example_v2.yxmd

surajmthomas

@AngelosPachis @messi007 Thank you both very much for the inspiration and your time for your solutions. You both are geniuses. I have worked on the solution that I was looking for based on your inputs. This is what I was quite looking for and it works like a charm

New_Toxic_Exception.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703