topic Little Syntax trouble shorten names in QlikView

Little Syntax trouble shorten names

Thu, 02 Dec 2010 15:17:50 GMT

Hello you all,

Situation: I've got 3 regions and I want to shorten the region names. Now they are called region losser, region almelo and region enschede and I want to get rid of the word region. My script only works for 1 region. Can someone help me with the correct syntax?

(Trim([DTZC Region])= 'Region Losser', 'Losser',[DTZC Region]) , IF(Trim([DTZC Region])= 'Region Almelo', 'Almelo',[DTZC Region]) , IF(Trim([DTZC Region])= 'Region Enschede', 'Enschede',[DTZC Region]) as [DTZC Region],

Little Syntax trouble shorten names

Thu, 02 Dec 2010 15:20:38 GMT

Check out 'map' and 'mapping' in the help.

Regards,

Gordon

Little Syntax trouble shorten names

jonathandienst — Thu, 02 Dec 2010 15:34:11 GMT

Richard

If you don't want to go the mapping approach (which would be better for many regions, but not so critical for 3), then use this expression:

If (Trim([DTZC Region])= 'Region Losser', 'Losser',
If(Trim([DTZC Region])= 'Region Almelo', 'Almelo',
If(Trim([DTZC Region])= 'Region Enschede', 'Enschede','')))

OR even

Replace([DTZC Region], 'Region ', '')

Jonathan

Little Syntax trouble shorten names

Thu, 02 Dec 2010 16:33:48 GMT

Thx Jonathan, this works perfect!

Little Syntax trouble shorten names

Thu, 02 Dec 2010 17:17:24 GMT

Got another one. It seems that I do not have 3 Regions but 4. The fourth is called Amsterdam (without the word Region). With the formula you gave me, I now only see Region Losser, Almelo and Enschede. The fouth one is missing. Is there a way to get it without naming it into the formula? I do not want to name it because in the futuren there will be more regions without the word Region.

thx in advance

Little Syntax trouble shorten names

jonathandienst — Thu, 02 Dec 2010 20:25:33 GMT

Richard

Use this:

If (Trim([DTZC Region])= 'Region Losser', 'Losser',
If(Trim([DTZC Region])= 'Region Almelo', 'Almelo',
If(Trim([DTZC Region])= 'Region Enschede', 'Enschede',[DTZC Region])))

Jonathan

Little Syntax trouble shorten names

hector — Thu, 02 Dec 2010 22:24:31 GMT

Hi, if the field value is always "Region XXXX" there are other options

SubField(FIELD,' ',2) as [DTZC Region]
right(FIELD,Index(FIELD,' ',1)-1)

just to expand the options 😉

rgds