topic Re: Removing a character from the field values in QlikView

Removing a character from the field values

Anonymous — Tue, 06 Oct 2015 05:16:33 GMT

I have a field with Sales values like 1.5 M, 2.5 M etc.,

How to remove M suffix from a field?

The reason for the removal is, Qlikview automatically takes the M character and converts it into some values. Instead I need 1.5, 2.5 as it is.

Re: Removing a character from the field values

tresB — Tue, 06 Oct 2015 05:19:32 GMT

Mid(Sales, Len(Sales)-1)

And note, 'M' might be for Million

Re: Removing a character from the field values

PrashantSangle — Tue, 06 Oct 2015 05:22:10 GMT

Hi,

Use purgechar()

try like

purgechar(fieldName,'M')

Regards

Re: Removing a character from the field values

ChennaiahNallani — Tue, 06 Oct 2015 05:25:20 GMT

Replace(YourFiledName,'M','''')

Re: Removing a character from the field values

prma7799 — Tue, 06 Oct 2015 05:25:55 GMT

Hi ,

As Tresesco said 'M' might be for Million.

can you please check with chart properties Number format >million symbol

Thanks

Re: Removing a character from the field values

nagarjuna_kotha — Tue, 06 Oct 2015 05:41:53 GMT

PFA..and If not enough u can use Replace Function also.

Re: Removing a character from the field values

sujeetsingh — Tue, 06 Oct 2015 05:46:46 GMT

Replace function will do your work.

Re: Removing a character from the field values

krishna20 — Tue, 06 Oct 2015 07:04:10 GMT

Hi,

Try Subfield(). It may works.

Re: Removing a character from the field values

Gabriel — Tue, 06 Oct 2015 09:01:01 GMT

Hi,

Please use PurgeChar(FieldName,'M')

Re: Removing a character from the field values

amit_gupta — Tue, 09 Oct 2018 16:36:11 GMT

Hi Koushik,

In case of Just removing character from String you can use this:

PurgeChar(text, remove_chars)

Example Result:

PurgeChar ( 'a1b2c3','123' ) Returns 'abc'

PurgeChar ( 'a1b2c3','312' ) Returns 'abc'

In your Case:

PurgeChar ( '1.5 M','M' ) Returns '1.5'

For all values

PurgeChar ( Sales,'M' )

Note: It will remove the perticular char from your string.

However, I feel that in your case Sales is measure data, you need to put condition you convert into million by multiplying 1000000.

I hope, it will help you