If in calculater dimension

[#1]

Hello,

I'm having trouble creating a calculated dimension.

What I want to say is :

In an existing dimension, if the text value contains either :

a number followed by _

or

the letter D followed by a number

or

TM_

then select these values and name this sub-dimension A'A'A'.

Can anyone help me out ?

Thank you very much

J

[Oleg_Troyansky]

Jonathan,

it looks like your data needs a serious cleansing... I recommend that you perform all those logical conditions in the load script and not in a Calculated Dimension, for a number of reasons, primarily - performance. Calculated Dimension is a #1 performance killer, and here you will be coupling it with 3 IF statements, comparing strings, and approx. 6 string functions. This Calculated Dimension won't perform even on a small dataset...

Now, when you approach this issue in the load script, you can use string functions (search on-line help for "string functions") such as:

index(), mid(), etc... and perhaps a logical function IsNum() to determine if a certain character is a number.

As an example of your first condition:

IF (index(MyField, '_') >1 and IsNum(mid(MyField, index(MyField, '_')-1, 1), ..., ...)

This condition literally verifies that the string contains '_' and that the character located before the '_' is a numbers. Your condition might be even more complex if you can have multiple occurences of an '_' within the string - in that case, you might have to use WHILE to verify all of them.

cheers,

Oleg