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

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

nareshthavidishetty — Sat, 16 Nov 2024 21:18:27 GMT

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

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

Vegar — Tue, 05 Mar 2019 11:44:51 GMT

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
;

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

nareshthavidishetty — Tue, 05 Mar 2019 12:02:07 GMT

Hi,

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

Thanks...

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

raman_rastogi — Tue, 05 Mar 2019 12:24:29 GMT

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

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

nareshthavidishetty — Tue, 05 Mar 2019 12:34:30 GMT

Resolved! by using the below code.

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

Thanks to Sunny Talwar.