Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

Apply formula for individual groups

amrutkasyap
6 - Meteoroid

Hello

 

RowIdFirsNameLastNamePIDTypeKeyOther1Other2Status
1JohnDoe18177101K1----S1
2John 2Doe 21817745K1  S2
3John 3Doe 31817846K1   
4John 3Doe 318177101K1  S1
5J2D218177200K1   
6J3D318178200K1   
7G1B119155200K2   
8G2B219155101K2   
9G2B219155102K2   
10G1B119155200K3   
11G2B219155101K3  S1
12G2B21915545K3  S2

 

  1. I want group the records by "Key" and check if the grouped records have Type (101 or 102) and (45 or 46). If the group has either of those types then, select that group of records. In the e.g. shown K2 will be eliminated by this process
  2. Next step is to apply formulas to the grouped subset.

E.g. Want to group by "Key" and apply formula to modify status -

.e. if type = 101 then Status = S1

if type = 45 then Status = S2

 

Please let me know if this is not clear.

 

Thank you

john_miller9
11 - Bolide

@amrutkasyap ,

 

You can solve this within the formula tool using conditional logic.  Below is an example, but you can build it out for your use-case. 


Another option is to use the initial logic (Key = 'K1' AND Type In (101, 45, ...) and apply the formula logic after that. If needed, you could even union the filtered out dataset back after you apply the formula.

 

Formula for Groups.png

Lwt08
6 - Meteoroid

I create a simple workflow that is fairly rough. You could probably reach the same endpoint with fewer tools but this is how I thought to do it. I hope this helps and feel free to ask any questions you have on it.

 

I abbreviated the data to only the columns that mattered for this question. I am first manipulating the data by changing the 102 and 46 to 101 and 45 because they are roughly the same for the desired checking and make the sorting formulas easier. Then I just check which keys do indeed match both and filter out the keys that don't using the join then just sort and add the status column.

grazitti_sapna
15 - Aurora
15 - Aurora

Hi @amrutkasyap,

 

Here is the solution to your query.

grouping.PNG 

Sapna Gupta
JT01
5 - Atom

Here's one that works by checking the each key group for both types.

 

Basically, it concatenate the types present for each key group, and checks the concatenate to see if they contain a number from (45,46) and one from (101,102).

 

clipboard_image_0.png

Labels