topic Re: Dropping text from a string in QlikView

Dropping text from a string

Mon, 14 Dec 2015 11:15:44 GMT

Hi ... I want to load the below field, 'SALES_NUMBER', but excluding the string 'AB' wherever it exists.

222100

223190

AB224190

AB225190

So output should be :

222100

223190

224190

225190

Re: Dropping text from a string

sunny_talwar — Mon, 14 Dec 2015 11:16:42 GMT

Try

KeepChar(SALES_NUMBER, '0123456789') as SALES_NUMBER

KeepChar() is the function, misspelled it intially

Re: Dropping text from a string

arulsettu — Mon, 14 Dec 2015 11:16:43 GMT

if you want only numbers try this

keepchar(SALES_NUMBER,'0123456789')

Re: Dropping text from a string

Anonymous — Mon, 14 Dec 2015 11:18:43 GMT

use KeepChar() instead of KeepChart() as suggested by Sunny

Re: Dropping text from a string

sunny_talwar — Mon, 14 Dec 2015 11:20:02 GMT

Hahahaha I don't see KeepChart()

Just kidding, I saw my mistake and fixed it

Re: Dropping text from a string

Anonymous — Mon, 14 Dec 2015 11:23:22 GMT

or try like this at script level, if it is in fixed format:

if(istext(mid(SalesField),1,2), mid(SalesField,3,len(SalesField)), SalesField) as SalesField

Re: Dropping text from a string

robert_mika — Mon, 14 Dec 2015 11:44:16 GMT

PurgeChar('AB224190','AB')

Feeling Qlikngry?

How To /Missing Manual(23 articles)

Re: Dropping text from a string

Anonymous — Mon, 14 Dec 2015 11:55:47 GMT

I was also kidding, Becoz I knew that before putting my comment for you, you would have been updated that

Re: Dropping text from a string

its_anandrjs — Mon, 14 Dec 2015 12:03:30 GMT

Try this way also

LOAD *,PurgeChar(COLA,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') AS KleenField;

LOAD * Inline

[

COLA

222100

223190

AB224190

AB225190

];

Regards

Anand

Re: Dropping text from a string

Mon, 14 Dec 2015 16:32:12 GMT

Thank you all. I used the first solution ie the keepchar function and it worked nicely.

What if now however I only wanted to pick up values which included the AB string ? So my output would return only the following :

AB224190

AB225190

Re: Dropping text from a string

sunny_talwar — Mon, 14 Dec 2015 16:35:12 GMT

Use a Where Statement in that case:

LOAD YourField

FROM Source

Where Left(YourField, 2) = 'AB';

Re: Dropping text from a string

Tue, 15 Dec 2015 11:34:58 GMT

Thanks Sunny your answers are spot on as usual. .

Robert I will bear in mind the purgechar function for future ... thanks

Re: Dropping text from a string

sunny_talwar — Tue, 15 Dec 2015 11:49:17 GMT

I am glad I was helpful