topic Re: Separate Number and Text in QlikView

Separate Number and Text

zwyne — Wed, 25 Nov 2020 16:16:04 GMT

Hello Experts,

PFA

In attached file I have column called Value Local Currency which is a mixed column. So I am trying to separate value and text but I am getting wrong results. I have used the following statement as below:

KeepChar([Value Local Currency],'0123456789') AS number,
PurgeChar([Value Local Currency],'0123456789') AS text

Please assist.

Thanks

Re: Separate Number and Text

avkeep01 — Thu, 08 Feb 2018 14:21:59 GMT

Hi Bafana,

Try SUBFIELD([Value Local Currency],' ZAR',1) AS Value

That will retrevie the part before " ZAR", which is the value.

Re: Separate Number and Text

tunoi — Thu, 08 Feb 2018 14:26:54 GMT

try these:

right([Value Local Currency],len([Value Local Currency]) - index([Value Local Currency], ' ')) as Currency

and

num(left([Value Local Currency],index([Value Local Currency], ' '))) as [Expected Value]

Re: Separate Number and Text

neelamsaroha157 — Thu, 08 Feb 2018 14:44:39 GMT

Hi Bafana,

Check this out.

Let me know if you find it helpful.

Thanks

Re: Separate Number and Text

surendraj — Thu, 08 Feb 2018 14:49:26 GMT

Simply You can

subfield([Value Local Currency],' ',1) as Number

subfield([Value Local Currency],' ',2) as Text

Re: Separate Number and Text

zwyne — Fri, 09 Feb 2018 13:13:55 GMT

Thank you everyone for the assistance.