topic Re: How to put null data into NA in QlikView

How to put null data into NA

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to put null data into NA

Anil_Babu_Samineni — Tue, 16 Jan 2018 13:23:22 GMT

Please post sample data and result you want to see?

Re: How to put null data into NA

Anonymous — Tue, 16 Jan 2018 13:30:24 GMT

Hii

Sample field name is Management

Management

[]

["aaaa"]

["rrrrrrrr"]

["b bbb "]

["qqqqqq, The - Proper"]

["zzzzzzz, The - Proper"]

["xxxxxxxxxxxx"]

excapted o/p is

Management

aaaaa

rrrrrrrr

qqqqqq,The-Proper

zzzzzzz,The-Proper

xxxxxx

Re: How to put null data into NA

Anil_Babu_Samineni — Tue, 16 Jan 2018 13:37:46 GMT

May be this?

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

Re: How to put null data into NA

Anonymous — Wed, 17 Jan 2018 05:58:34 GMT

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

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

Re: How to put null data into NA

Anil_Babu_Samineni — Thu, 18 Jan 2018 14:55:33 GMT

Use Subfield() for Measure and then use

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