Formula for selecting data in different groups based on multiple criteria

[tbrewski123]

I am looking for a way to identify items within different categories based on two or more attributes.  For example, a customer sales multiple products in category 1 (ex. Home Products) and category 2 (Office Products).  Within each of those categories competitors also sale their products. 

The customer has price data and rating data for their products and competitor products.  They want to determine if the have a price and rating advantage for each of their products compared each of the competitor products to in each category.  So if their product has a lower cost and a higher rating than a particular competitor product they have an advantage.  I included an example of what I am thinking below

So for MyProduct_1 in the home category they would want to compare their rating and cost to each competitor product (Other1, Other2, etc.)  Then do the same thing again for MyProduct_2.  Then repeat the same for each category.  I am not sure if I need to create a formula for each separate category or if their is a way to do this in one formula and loop until the analysis for all categories is complete.  I am still new to Alteryx so I am not sure about the best way to go about this.  Any help would be appreciated.

Category	Product	Brand	Rating	Cost
Home	MyProduct_1	MyBrand	4	100
Home	MyProduct_2	MyBrand	3	115
Home	Other1	TheirBrand	3	125
Home	Other2	TheirBrand	5	95
Home	Other3	TheirBrand	2	120
Home	Other4	TheirBrand	3	100
Home	Other5	TheirBrand	4	110
Office	MyProduct_A	MyBrand	4	25
Office	MyProduct_B	MyBrand	3	35
Office	MyProduct_C	MyBrand	5	30
Office	Other_A	TheirBrand	3	60
Office	Other_B	TheirBrand	4	20
Office	Other_C	TheirBrand	2	33
Office	Other_D	TheirBrand	1	30
Office	Other_E	TheirBrand	5	50
Office	Other_F	TheirBrand	2	40

[MichalM]

@tbrewski123 

Would the below work? I'm assuming it's just 2 brands - my brand and competitor brand

• Split the data into a stream per brand - you can do this using the Filter tool
• Join the two on Category field - this will give you all the combinations
• Use the Formula tool to compare the Ranking and Price

IF [Right_Rating]>[Rating] AND [Right_Cost] < [Cost] Then "Yes"
ELSE "No"
ENDIF

Workflow attached.

[tbrewski123]

Hello, thanks for the quick response.  The workflow you attached works when I run it, however, when I try to recreate it I cannot get it to work correctly.  The problem for me seems to occur in the join.  The filter separates the brand data but the "J" does not contain any output after I run it.  There is output in the "R" and "L".  Not sure what I am doing wrong.  Any help would be appreciated.

On a second note, would it be possible to do something similar if there was more than two brands?

Thanks

[MichalM · Accepted solution]

If there are no records coming out of J it means that there are no matches. If you have the Join configured correctly - join by specific field which should be the Category field on both ends and you can see the same category values in both streams (L and R) - it could be that there are leading or trailing white spaces in your data or the case could differ between the streams.

If you had multiple competitors you'd simply split the data into stream per competitor and join the data multiple times.

[tbrewski123]

Great, thanks!  I had the join set up incorrectly.  You solution worked.