topic Formatting field in QlikView

Formatting field

darren_dixon — Tue, 24 Sep 2013 09:47:20 GMT

I have a field called serviceArea which displays with __b, __d, __f etc. instead of spaces (Attached below).

Rather than writing a long if statement I was wondering if there was some formatting which could be applied in the script to tidy this up?

Thanks,

Darren

1st__bTouch__bMobile

A__fD__baccount

A__fD__bLogin

Accidents__b__u__bMS__bAccess

Allpay

Anti__bVirus

Apex__bAsset__bManagement

Asbestos__bManagement

Asta__bPowerproject

Audio__bVisual

Autocad__fTeamwork__fPlotter

Re: Formatting field

bobdawes — Tue, 24 Sep 2013 12:31:13 GMT

My guess is you're best bet is using a replace() string function. The example from help:

replace('abccde','cc','xyz') returns 'abxyzde'

you'll want something along the lines of replace (serviceArea,'__b',' '), though you will probably want to embed several replaces to handle __d and __f and any other examples you have.

Using purge() function to simply remove underscores won't work, as you'll be left with the extra letters (d, f, etc.)

Re: Formatting field

markmccoid — Tue, 24 Sep 2013 13:18:19 GMT

What is the final view of the data that you need?

Are you trying to replace those __b, etc or use them as delimiters and split the field into multiple fields?

Re: Formatting field

darren_dixon — Tue, 24 Sep 2013 14:08:56 GMT

I'm looking to replace __b, __d, __f.

The replace(serviceArea,'__b',' ') is great but how would I do this for all three.

Replace(Service__bArea,'__b',' ') AS Test,

Re: Formatting field

nizamsha — Tue, 24 Sep 2013 14:40:28 GMT

=PurgeChar(serviceArea,'-') its in listbox

=replace('abc-cd-e','-','') it will work in text object

Re: Formatting field

tresB — Tue, 24 Sep 2013 14:52:06 GMT

May be like this?

Replace(Replace(Replace(ServiceArea, '_b', ' '), '_d', ' '), '_f' , ' ')) as NewServiceArea

Not very dynamic, but this is what it comes in mind now.

Re: Formatting field

Anonymous — Tue, 24 Sep 2013 15:02:17 GMT

use an If(

serviceArea = '_b',

Replace( ...),

if(

serviceArea ='_c'

Replace(...),

Replace (..case for _d)

)

) as Test,