How to put null data into NA

[Anonymous]

Hii experts

where ever Management null data is there  coming "NA"  and remaining as usual Data and here also mutiple Replace funcation i am using is there any way single Replace function all special Character removed.

I tried below expression ..

if(trim(Replace(replace(Replace(Replace(Trim([Management ]),'[',''),']',''),'"',''),'\u0026','&'))=0 or Trim(isnull([Management ])),'NA',(Replace(replace(Replace(Replace(Trim([Management ]),'[',''),']',''),'"',''),'\u0026','&'))) as Mgmt1,

I am not getting exatly o/p

Thanks

[Anil_Babu_Samineni]

Please post sample data and result you want to see?

[Anonymous]

Hii

Sample field name is Management

Management

[]

["aaaa"]

["rrrrrrrr"]

["b bbb "]

["qqqqqq, The - Proper"]

["zzzzzzz, The - Proper"]

["xxxxxxxxxxxx"]

excapted o/p is

Management

NA

aaaaa

rrrrrrrr

qqqqqq,The-Proper

zzzzzzz,The-Proper

xxxxxx

[Anil_Babu_Samineni · Accepted solution]

May be this?

If(IsNull(Replace(TextBetween(Management,'"', '"'),' ','')),'NA',Replace(TextBetween(Management,'"', '"'),' ','')) as Management

[Anonymous]

Hii

Sample field name is Management

Management

[]

["aaaa"]

["rrrrrrrr"]

["b bbb "]

["qqqqqq, The - Proper,"]

["zzzzzzz, The - Proper"]

["xxxxxxxxxxxx"], ["XXX - Chief Global Equity Strategist \u0026 Head of Macro Research Europe","XXX - Vice Chair of GS Japan, Co-head of Macro Res Asia, \u0026 Chief JP EQ Strategist","XXXX - Chief US Equity Strategist"]

excapted o/p is

Management

NA

aaaaa

rrrrrrrr

qqqqqq,The-Proper

zzzzzzz,The-Proper

xxxxxx

XXXChiefEquity Strategist &Head of Macro Research Europe

XXX - Vice Chair of GS Japan, Co-head of Macro Res Asia&Chief JP EQ Strategist

XXXX - Chief US Equity Strategist

How to put null data into NA

[Anil_Babu_Samineni]

Use Subfield() for Measure and then use

If(SubStringCount(TextBetween(Management, '[',']'),'XXX'),SubField(TextBetween(Management, '[',']'),','),TextBetween(Management, '[',']'))