Categories based on the character

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.

Screenshot 2024-01-31 010616.png

Many thanks in advance.

Mapping Series.xlsx

Common Use Cases

Accepted answers

rzdodson

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

All comments

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!

Solution.yxzp

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).

Solution.yxzp

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

@SH_94: @aatalai is spot on. Initially, when you do a ChartoInt formula, it'll convert A to 65, B to 66, etc. To get it back to its desired value, I subtracted 64 so we can get the numbers aligned to the letter.

As a note, if any of the solutions provided by @binu_acs or myself solved the use case, please go ahead and mark them as solutions to make it easier for others to find if they run in to something similar.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703