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.
Solved! Go to Solution.
Hi @mystasz
Yes, its possible via Filter tool, Join and Multi row formula..
Please find the solution below.
Shanker V
Thank you. I don't see a solution, can you try reattaching?
Would you be able to attach your workflow so I can see whats in the joins and the formulas?
Step 1: Insert Combination data
Step 2: Use filer to include only Clinical staff
Step 3: Use the Record ID tool
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
Step 8: Formula tool
Next join both formula tools
Next comes the last formula tool
Used Select tool to remove the unwanted columns
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.