Custom Binning in Alteryx

[AQILKURJI]

Hi all, 

I am trying to figure out how to bin values in alteryx using custom range. I have an "age" field and some how i want to group them in bins of:

- 0 to 17 

- 18 to 29 

- 30 to 69

- 70 to 79

- 80 + 

Is there any way i can bin these and give this a new field name i.e " Age Group".

I have tried using the multiple binning tool but it dose't allow me to specify values. 

Thanks, 

Aqil 

[JohnJPS · Accepted solution]

Hi @AQILKURJI,

A formula tool can do this: new field "AgeGroup" with:

IF [Age] <= 17 THEN
  "Group1"
ELSEIF [AGE] <= 29 THEN
  "Group2"
ELSEIF [AGE] <= 69 THEN
  "Group3"
ELSEIF [AGE] <= 79 THEN
  "Group4"
ELSE
  "Group5"
ENDIF

 ... where the values are whatever you want, whatever field type you want.

Hope that helps!

John

[AQILKURJI]

Thanks John !

[lcm3k]

Hi there, 

I tried something similar but receive an error (Invalid type in operator) when I complete the IF statement. Do you know why that would be the case and how I can fix?

IF [Spend] >= 75000 THEN 'Large Spend'
ELSEIF [Spend] < 75000 AND [Spend] >= 10000 THEN 'Medium Spend'
ELSE 'Small Spend'
ENDIF

[JohnJPS]

Hi @lcm3k,

I'd have to see the workflow, but some ideas:

1. If you're trying to replace the numeric [Spend] field with character datat ("Large Spend"), Alteryx won't like that. Instead, create a new character field ("SpendSize" or some such) and use the same formula you've shown.

2. If [Spend] isn't specified as numeric (even though it looks numeric), you could get that error.  Use the Select tool to force it to a numeric type; if it fails that due to some non-numeric rows, find those rows and eliminate them; (see the IsNumber function for help with that: us it in a filter to separate the non-numeric rows for analysis).

Hope that helps!

John