break out values to form new dimension from existing dimension

[#1]

hi!

if i have the following saleslines, can i break out 3 sub categories to filter on, ie.

SalesPerson                     Amt

Bradley_DiscountOrder     100

Bradley_DirectOrder          200

Bradley_InititalOrder          300

John_DiscountOrder          50

John_DirectOrder              30

Suzie_InitialOrder             400

to form a new dimension with the following members;

Dimension OrderType:

Members, DiscountOrder;DirectOrder;InitialOrder

i was considering using right function, but not always the length is the same.

advise is appreciated.

Best

Brad

[hic · Accepted solution]

If you use the Subfield() function, you can extract these values from the string. E.g.

     Subfield(SalesPerson,'_',2) as OrderType

HIC

[#3]

Hi Henric!

thank you for the correct answer.

in my "real life" scenario however i have a situation whereby there is not an explicit _ that symbolizes the position of the subfield, or the same amount of ' '  in the text string , instead it looks something like this.

is there any workaround for grouping the names instead of going by position?

Best

Brad

[hic]

If you know that your'e looking for exactly DiscountOrder, DirectOrder or InititalOrder, then you can look for these strings using the Index() function, e.g.

If( Index(SalesPerson, 'DiscountOrder'), 'DiscountOrder',

If( Index(SalesPerson, 'DirectOrder'), 'DirectOrder',

If( Index(SalesPerson, 'InititalOrder'), 'InititalOrder',

If( Index(SalesPerson, 'Order'), 'Other Order',

'No Order')))) as OrderType

HIC

[hic]

Or if you know that it is the last word, then you can use

     Subfield(SalesPerson,' ', -1) as OrderType

HIC

[#6]

Thank you Henric, i appreciate your help, worked very well.

Trevlig helg.

Brad