Beast mode for help Sorting column

TMonty0319

I need a beast mode so i can sort a column. I need to take the number out of this Engine column so then can sort in order. I have values like this

Engine 1

Engine 2

Plant Engine 1

Engine 3

Plant Engine 2

Engine 4

Engine 5

I figure if i can just do a formula to take out the number then i can sort them together based on number

Sean_Tully

There may be an easier way, but I usually write a beast mode to assign a number to each string value, like:

case when engine = 'Engine 1' then 1

when engine = 'Engine 2' then 2

when engine = 'Plant Engine 1' then 3….etc.

This may not be ideal if the values of the string change or keep growing over time.

If the number is always the last character of the string, I think you can do right(`engine`, 1), but if the data is similar to your sample, you'll have two 1's, two 2's, etc.

david_cunningham

If you have a lot of different combinations and don't want to do this manually, an option is to do it in ETL with REGEXP_REPLACE.

REGEXP_REPLACE(string, '^.(\d).$', '$1')*1

Sean_Tully

There may be an easier way, but I usually write a beast mode to assign a number to each string value, like:

case when engine = 'Engine 1' then 1

when engine = 'Engine 2' then 2

when engine = 'Plant Engine 1' then 3….etc.

This may not be ideal if the values of the string change or keep growing over time.

If the number is always the last character of the string, I think you can do right(`engine`, 1), but if the data is similar to your sample, you'll have two 1's, two 2's, etc.

david_cunningham

If you have a lot of different combinations and don't want to do this manually, an option is to do it in ETL with REGEXP_REPLACE.

REGEXP_REPLACE(string, '^.(\d).$', '$1')*1