Iterating to Find Combinations

[mystasz]

Hello Community,

In the attached document, I have a dataset (CombinationsData tab) which is just a reference table to find all the different combinations. Then I have my data (ActualData tab) which displays my raw data. For each combination of SecurityPoint1 and SecurityPoint2, I have to look it up in the ActualData to see if both points exist AND the JobRole matches. 

If they match, I need to take the Violation from CombinationsData tab and apply it to my output. 

Example:

Under CombinationsData for Templates ID T00379 and T10101, JobRole Clinical Staff and Surgeon, Security Point 1 = 124 and Security Point 2 = 33 exists

Under ActualData Security Points column 124 and 33 exist for Clinical Staff only (highlighted in yellow)

In the Expected Output, only the Clinical Staff row should populate

Any idea how I can approach this? I have about 60k combinations and 5 million rows of data in my actual dataset. 

[ShankerV]

Hi @mystasz 

Yes, its possible via Filter tool, Join and Multi row formula..

Please find the solution below.

Shanker V

[mystasz]

Thank you. I don't see a solution, can you try reattaching?

[ShankerV]

Hi @mystasz 

Seems some issue during the post. 

Few sec, pasting the solution once again.

Shanker V

[ShankerV · Accepted solution]

Hi @mystasz 

Solution attached below:

Shanker V

[mystasz]

Would you be able to attach your workflow so I can see whats in the joins and the formulas?

[ShankerV]

Step 1: Insert Combination data 

Step 2: Use filer to include only Clinical staff

Step 3: Use the Record ID tool 

[ShankerV]

Step 4: Input Actual data

Step 5: Use the join L node from Record ID

R node from Actual data

Step 6: Create one more join with same L and R node

But join by specific fields will be different

[ShankerV]

Step 8: Formula tool

Next join both formula tools

Next comes the last formula tool

Used Select tool to remove the unwanted columns

[ShankerV]

Hi @mystasz 

I have explained the solution, can you please replicate the same in your Alteryx.

Sorry couldn't attach the workflow as my Alteryx version is not updated, so couldn't save the workflow.