How to bin data?

mkhtran

I have some data in the form of

Field1 (string)	Field2 (int)
foo	12
bar	78
baz	62

I'd like to add another Field that depends on Field2, which would work similar to a Formula tool with

Output Column: Category (string)

IF     [Field2] <= 25  THEN "TINY"
ELSEIF [Field2] <= 50  THEN "SMALL"
ELSEIF [Field2] <= 75  THEN "MEDIUM"
ELSEIF [Field2] <= 100 THEN "BIG"
ELSE                        "HUGE"
ENDIF

The problem is that my categories and cutoffs are coming as data from elsewhere in the workflow, like

Category Level	Category Name	Category Cutoff
1	TINY	25
2	SMALL	50
3	MEDIUM	75
4	BIG	100
5	HUGE	999

What's the best way to achieve this?

OllieClarke

@mkhtran 

Here's another approach using the Dynamic Replace tool, which doesn't require cross-joining your data

I use the multi-row to generate a boolean expression

Having already created a blank Category field in the original data stream, and written its name as data in the second stream, I can replace that value using the dynamic replace tool:

@Qiu's approach is easier, and requires fewer tools, but if you have a big dataset and lots of category values, you will create a lot of unnecessary data.

Ollie

CategoryCutoffs.yxmd