Categories based on the character

[SH_94]

Hi community, 

i would like to ask how to make the categories below based on the input.

The logic will be as follows:

If the first character of the company code start with from A till H, it will be mapped under AH series. if the first character fall under I to R , then it will be mapped under IR series. If the first character from S to Z, then it will be mapped under SZ series.

Many thanks in advance.

[rzdodson]

@SH_94 attaching several options for you. The first one is the most aligned solution to what you are looking for. The others rely on a custom reference table that is built out. In the latter solutions, if your requirements change based on specific company codes (e.g. DA01 needs to go to SZ series), then it'll allow you to make those specific changes and have your data reflect.

Hope that gets you on the right track! :)

[aatalai]

@SH_94 

Try this in the formula tool

If left([company code],1)="A" or left([company code],1)="B" or ... left([company code],1)="H" Then "AH"

elseif left([company code],1)="I" or left([company code],1)="J" ... left([company code],1)="R" Then "IR"

else "SZ" endif

[SH_94]

Hi @rzdodson ,

i think this method not working for me as i dun have to use the text input to append the field.  i plan to just build dynamic formula in the formula for this purpose.

Do you know how to do this?

Thank you

[aatalai]

@SH_94 have you tried the formula method I suggested?

[SH_94]

Hi @aatalai ,

This method i need to type one by one, is there any way more dynamic to build the formula?

[rzdodson]

@SH_94 attaching a Formula-only based solution for you (See Option 0: Final container).

[aatalai]

@rzdodson was looking up how to convert alaphabet to number; you beat me to punch great minds think a like 😂

[SH_94]

Hi @rzdodson ,

Thank you for the prompt response. This is the formula that i look for. May i know why we need to minus 64 in this case?

Thanks

[aatalai]

@SH_94 it's to do with the Unicode® character https://help.alteryx.com/current/en/designer/functions/conversion-functions.html#idm45934712704128

A is 65 from memory and -64 makes it 1