In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Evaluating multiple rows across unique ID

mystasz
8 - Asteroid

Hi Community,

 

For the below given dataset, I'd like to evaluate the "value" column by "ID" and assign a violation. 

 

Below is the logic:

- Create a new column called "Violation"

- If ID = ST01 and value is 1 AND 4 - Label as "Violation 1"

- If ID = ST01 and value is 2 AND 7 - Label as "Violation 2"

- If ID = ST02 and value is 1 AND10 - Label as "Violation 3"

- If ID = ST02 and value is 2 AND 3  - Label as "Violation 4"

- If ID = ST02 and value is 6 AND 9 - Label as "Violation 5"

 

Sample and expected data attached. 

5 REPLIES 5
messi007
15 - Aurora
15 - Aurora

@mystasz,

 

Please see below :

 

messi007_0-1655749218091.png

 

Attached the workflow,

Regards,

mystasz
8 - Asteroid

Hello and thank you. The formula used is applying "OR" logic and evaluating each row individually. For 1 AND 4, this logic would say its a violation if only 1 existed and not 4. Both 1 and 4 need to exist for a single ID. 

 

If you change the number 4 to a 20 for ST01, it's still assigning Violation 1 which would be untrue based on the logic. 

 

 

ArtApa
Alteryx
Alteryx

Hi @mystasz - Here is how you can achieve a desired outcome:

 

ArtApa_0-1655765371770.png

 

mystasz
8 - Asteroid

This worked perfectly! Thank you

Qiu
21 - Polaris
21 - Polaris

@mystasz 
@ArtApa gives a very smart solution, but I would also like to presenta pretty length one but I believe which is more straightforwad and maybe easy to modify when you have some input changes.
I used two Batch Macro to list up the Combination of Values for each ID and name then do the conditional checking.

0621-MysticalAz.PNG

Labels
Top Solution Authors