Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

break out values to form new dimension from existing dimension

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

1 Solution

Accepted Solutions
Highlighted

Re: break out values to form new dimension from existing dimension

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

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

HIC

View solution in original post

5 Replies
Highlighted

Re: break out values to form new dimension from existing dimension

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

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

HIC

View solution in original post

Highlighted
Not applicable

Re: break out values to form new dimension from existing dimension

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

Highlighted

Re: break out values to form new dimension from existing dimension

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

Highlighted

Re: break out values to form new dimension from existing dimension

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

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

HIC

Highlighted
Not applicable

Re: break out values to form new dimension from existing dimension

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

Trevlig helg.

Brad