Group based on the different fields

[RM_XYRETLA]

This is my first post as I am struggling to find solutions via google and other posts. I want to group the records based on pre-defined rules. Please see the rule and expected results below. The challenge to me is that the rule is based on different fields in the data i.e. color and quality, color and size, size and quality. Thank you in advance!!!

Rule: 

Rule
Condition	Group
Color = Green, Quality = Bad	1
Color = Blue, Size = L	2
Color = Red, Quality = Good	3
Color = Yellow, Size = M	4
Size  =  S, Quality = OK	5

Results to achieve:

Color	Size	Quality	Group
Green	M	Bad	1
Blue	L	OK	2
Blue	L	OK	2
Red	S	Good	3
Red	L	Good	3
Green	M	Bad	1
Blue	L	OK	2
Yellow	M	OK	4
Red	S	OK	5

[Cremo]

Hi @RM_XYRETLA ,

you could use a simple Formula tool with the specified conditions defined in a IF-THEN-ELSE structure. You could give a look at the attached workflow. Hope it helps :)

 

[ChrisTX]

How many Rules do you have?  A small enough number, where an IF function would be easy to write and maintain?

If you have many Rules, I'm guessing you're asking about a how to configure a Dynamic IF function... Consider using a macro, where you would replace the inner XML of an IF function within a Formula tool.

Can you post sample input and expected output? 

Here are a few posts about updating XML using a batch macro:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Transpose-Key-Field-Summary-Fi...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/List-box-with-Transpose/td-p/514539

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Unknown-Select-Feature-w-Summarize-Too...

Start by becoming familiar with the XML structure of the Function you'll need to use.

Under Options > User Settings > Edit User Settings > Advanced, check the box for Display XML in Properties Windows.

Then you can see the XML for each tool.

 

 

 

Chris

[RM_XYRETLA]

Thank you for the help. The example I provided was just a simple demo of what I tried to achieve. @ChrisTX, you are right. In the real dataset, there are 14 fields (so the comb can be indefinite). The rules (use a mapping table to redefine fields and values) can be 100+, based on values from the combination of any two of those 14 fields. Sounds like it will be a challenging task to me to learn a new trick. 

[ChrisTX]

@RM_XYRETLA  please don't drip feed the test cases. It's not good for anyone's time.

If you have a complete list of test cases, provide them.

All the best,
Chris

[danilang]

Hi @RM_XYRETLA 

This looks like a good case of the Dynamic Replace tool.  You can configure the rules as the left input.   The main caveat is that the all the rules are applied to all the rows in order, so if you have nested rules you need to have the most restrictive one at the end of the list.  For example.  If rule 1 is f1=A and f2=B then YY and rule 2 is f1=A and f2=B and f3 =C then ZZ, you need to make sure that rule 2 comes after Rule 1.

Dan