Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator III

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

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..


1 Solution

Accepted Solutions
raman_rastogi
Partner
Partner

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

View solution in original post

4 Replies
Vegar
MVP
MVP

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
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
nareshthavidishetty
Creator III
Creator III
Author

Hi,

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

 

Thanks...

raman_rastogi
Partner
Partner

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
Creator III
Creator III
Author

Resolved! by using the below code.

 

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

Thanks to Sunny Talwar.