Convert field value which starts with text or some special characters into 'NLL'

[nareshthavidishetty]

Hi,

Can we make a field value which starts with text or some special characters into 'NLL' .

Field Product#:

950005-11300

950040-11300

91996171-11300

Dongsheng-11300

KINTZ PLAS-11300

N/A-11300

n/a-11300

NA-11300

Refer to a-11300

_XNA-11300Refer to a-11300
参考附件-11300

Thanks..

[Vegar]

You could use an IF WildMatch.  

LOAD
  IF(wildmatch( Product#, 'N/A*', 'NA*', 'Refer to a*', 'Dongsheng-*','参考附件-*'), 
     null(), 
     Product#) as Product#,
  Field1, 
  Field2,
  Measure1,
  Measure2
FROM 
  DATA
;

[nareshthavidishetty]

Hi,

I have thousands field values.I have posted a sample date.

Thanks...

[raman_rastogi · Accepted solution]

May like this.

Data:
Load * Inline [
FieldProduct
950005-11300
950040-11300
91996171-11300
Dongsheng-11300
KINTZ PLAS-11300
N/A-11300
n/a-11300
NA-11300
Refer to a-11300
参考附件-11300] ;

Load
if( IsNum(Left(FieldProduct,2)),FieldProduct,' ') as value,
FieldProduct
Resident Data;
Drop Table Data;

Regards

Raman

[nareshthavidishetty]

Resolved! by using the below code.

If(IsNum(Left(Product, 1)), Product, 'NLL') as Product

Thanks to Sunny Talwar.