Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

DATA PREPARATION LOGIC

Zaid
8 - Asteroid

Hi All,

 

I need some help with a logic. I have process, sub-process and status of sub process as columns. The logic is such I want the processes that have status only as "OPEN","WORK IN PROGRESS"  and "DRAFT". If any of the subprocess has anything apart from these,they should be filtered out and only the process having sub-process as the above three should be returned. Can this be be done using any of the tools of alteryx,like formula, multi-row,etc.

 

The data is something like

 

ProcessSubprocessStatus
AA1OPEN
AA2OPEN
AA3COMPLETED
AA4WORK IN PROGRESS
AA5DECATIVATED
BB1OPEN
BB2WORK IN PROGRESS
BB3OPEN
BB4DRAFT
CC1OPEN 
CC2WORK IN PROGRESS
DD1DEACTIVATED
DD2OPEN
DD3COMPLETED
DD4WORK IN PROGRESS

 

so i should get only B and C as my output. Process A and D has active subprocesses in other states apart from the ones i need.

 

Thanks

Zaid

4 REPLIES 4
Thableaus
17 - Castor
17 - Castor

Hi @Zaid 

 

Here's my solution

 

Solution.PNG

 

 

- Filter out records that don't have any of these status

- Join these filtered out records back to the dataset by the field "Process" - everything that is on the Right Side of the Join contains only one or more of these status.


Cheers, 

Zaid
8 - Asteroid

Hi

 

 

 

Thableaus
17 - Castor
17 - Castor

Hi @Zaid 

 

I guess one of the beautiful things of Alteryx is that there are many ways to do the same thing.

 

So I tried to find two more paths to reach your final solution.

 

Solution.PNG

 

Both solutions are conducted by a Formula Tool that indicates a flag if the row meets your desired conditions (1 or 0).

 

1st Solution:

 

- Gets the minimum value grouping by Process - that means, if it's 0 (one row of that process doesn't meet the conditions), then the whole process will return 0.

- Filter records with minimum value of 1 - all records meet the specified conditions

- Join the filtered Processes back to the original dataset, getting only records that passed that filter.

 

2nd solution (using multi-row formula tool with no Joins)

 

- Creates a Record ID to keep track of the records

- Sorts the Flag field previously created within the Processes.

- Multi-Row Formula tool indicates that if previous value = 0, then the Flag will turn to 0 inside that process (Notice that Multi-Row formula groups by Process and also the option "Values for Rows that don't exist" is checked with "Set to Values of Closes Valid Row" (this is to avoid turning the first record of a Process to 0 if it is actually 1) 

- Filters Flag record = 1 (that means none of those records of a Process turned to 0, a.k.a. they meet all conditions stated before).

- Reorders records by sorting Record ID.

- Uses Select Tool to leave original fields of the dataset.

 

Does that actually suit you? Workflow appended.

 

Cheers,  

Zaid
8 - Asteroid

Thanks for such a beautiful answer. Loved the way you took time out to explain the steps

 

Thanks

Zaid

Labels