topic Re: how to remove string values in a field? in QlikView

how to remove string values in a field?

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

I have one field like below it showing numbers and string values.so if requirement keep string in a field remove numbers or if requirement keep numbers remove strings.how?

data:

1203

4567

OBALT

9200

3456

22334

OEUROPE

like that.

data is field name.

Thanks

daisy

Re: how to remove string values in a field?

m_woolf — Fri, 09 Feb 2018 17:42:14 GMT

To delete the records where Data is a string:

load

from whatever

where num(Date) > 0

To delete the value of Data when is a string:

load

if(num(Data) > 0,Data) as Data 1

from Whatever;

drop field Data

rename field Data1 to Data;

Re: how to remove string values in a field?

swuehl — Fri, 09 Feb 2018 17:45:06 GMT

You can use IsNum() function to check if the field value is a number:

LOAD data

FROM ...

WHERE IsNum(data);

or for text values:

LOAD data

FROM ...

WHERE NOT IsNum(data);

Re: how to remove string values in a field?

shiveshsingh — Fri, 09 Feb 2018 17:54:21 GMT

T:LOAD * INLINE [

data

1203

4567

OBALT

9200

3456

22334

OEUROPE

] where IsNum(data);

Re: how to remove string values in a field?

Akina0929 — Sat, 10 Feb 2018 06:28:39 GMT

Hi,

use following expressions.

=purgechar(data,0,1,2,3,4,5,6,7,8,9) >>it Removes number from field.

=purgechar(data,'put all strings in single quote which you want to Remove') >> it Removes strings from the fields.

Regards

Anji

Re: how to remove string values in a field?

daisy1438 — Sun, 11 Feb 2018 03:20:36 GMT

thanks it working.

Re: how to remove string values in a field?

daisy1438 — Sun, 11 Feb 2018 03:24:59 GMT

Hi above function is working fine.

and some times my fields show another values also how to remove that.

input: output:

field Field

123 UK

111 US

Missing

it means while loading time remove null values and missing and numbers.

Re: how to remove string values in a field?

shiveshsingh — Sun, 11 Feb 2018 13:37:24 GMT

Yes, you can filter it if you don't require, during loading.

Re: how to remove string values in a field?

shiveshsingh — Sun, 11 Feb 2018 13:37:40 GMT

Please close the thread

Re: how to remove string values in a field?

swuehl — Mon, 12 Feb 2018 20:06:57 GMT

Just combine multiple conditions in your WHERE clause using boolean operators:

LOAD data

FROM ...

WHERE

NOT IsNum(data)

AND

Len(Trim(data)) // NULL will return zero / false here

AND

data <> 'Missing'

;

Re: how to remove string values in a field?

ychaitanya — Tue, 13 Feb 2018 01:09:38 GMT

Hello Daisy

load PurgeChar(data,'OBALTEUORPEABCDEFGHIHKLMN') as Data1,

KeepChar(data,'OBALTEUORPEABCDEFGHIHKLMN') as data2;

LOAD * INLINE [

data

1203

4567

OBALT

9200

3456

22334

OEUROPE

];

Please mark the posts as Helpful or answered to close the discussion so that it would be helpful for others in the community