topic Re: Isolate Numeric values and AlphaNumeric Values from a Field in QlikView

Isolate Numeric values and AlphaNumeric Values from a Field

kalyandg — Mon, 11 Sep 2017 05:46:31 GMT

Hi All,

I have a requirement, need to isolate Numeric values and alpha numeric values from a Field.

For Example:

Item Code

12-45A-PGH

PG-123-ABC

As above, Itemcode starts with Numeric and Alphanumeric, I want to create two fields using a condition as below

if (ItemCode starts with 'Numeric value', 'Exports', (specification - 0,1,2,3,4,5,6,7,8,9)

if(ItemCode starts with 'Alphanumeric' , 'Imports', (specification - A-Z)

Please help me to write specific condition to extract the information as above.

Thanks,

Kalyan

Re: Isolate Numeric values and AlphaNumeric Values from a Field

Anil_Babu_Samineni — Mon, 11 Sep 2017 05:54:47 GMT

May be this?

If(Left([Item Code], 1) = FindOneOf([Item Code],'0123456789'), 'Exports','Inports')

Re: Isolate Numeric values and AlphaNumeric Values from a Field

techvarun — Mon, 11 Sep 2017 06:10:09 GMT

Try below script

Test:

LOAD * INLINE [

ItemCode

12-45A-PGH

PG-123-ABC

];

Load KeepChar(ItemCode, '0123456789') as Import, PurgeChar(ItemCode, '0123456789-') as Export Resident Test

Re: Isolate Numeric values and AlphaNumeric Values from a Field

Peter_Cammaert — Mon, 11 Sep 2017 11:09:54 GMT

Maybe a very compact expression will do the job?

IF (ord([Item Code]) < 58, 'Exports', 'Imports')

Technique is based on the fact that the ord() function returns the ASCII value of the first character of the string passed as a parameter. All alphabetical characters have a value larger than 64. This works perfectly as long as every product code starts with an akphanumeric character.