Apply formula for individual groups

[amrutkasyap]

Hello

RowId	FirsName	LastName	PID	Type	Key	Other1	Other2	Status
1	John	Doe	18177	101	K1	--	--	S1
2	John 2	Doe 2	18177	45	K1			S2
3	John 3	Doe 3	18178	46	K1
4	John 3	Doe 3	18177	101	K1			S1
5	J2	D2	18177	200	K1
6	J3	D3	18178	200	K1
7	G1	B1	19155	200	K2
8	G2	B2	19155	101	K2
9	G2	B2	19155	102	K2
10	G1	B1	19155	200	K3
11	G2	B2	19155	101	K3			S1
12	G2	B2	19155	45	K3			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]

@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.

[Lwt08]

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]

Hi @amrutkasyap,

Here is the solution to your query.

 

[JT01]

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).