DATA PREPARATION LOGIC

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

Process	Subprocess	Status
A	A1	OPEN
A	A2	OPEN
A	A3	COMPLETED
A	A4	WORK IN PROGRESS
A	A5	DECATIVATED
B	B1	OPEN
B	B2	WORK IN PROGRESS
B	B3	OPEN
B	B4	DRAFT
C	C1	OPEN
C	C2	WORK IN PROGRESS
D	D1	DEACTIVATED
D	D2	OPEN
D	D3	COMPLETED
D	D4	WORK 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

Preparation

Accepted answers

Thableaus

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.

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,

WF1.yxmd

All comments

Thableaus

Hi @Zaid

Here's my solution

- 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,

WF1.yxmd

Zaid

Hi Thableaus,

The workflow that you have provided works fine and does the calculation that I was expecting. However, is there any other way of achieving this, whether this can be achieved by using formula tool or multi row formula and then filter.

Actually, I have a lot of other logics as well and this needs to be just added to the logic related to various columns.

Thanks

Zaid

Thableaus

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.

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.

- 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,

WF1.yxmd

Zaid

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

Thanks

Zaid

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703