topic Re: Need to replace blanks with null in QlikView

Need to replace blanks with null

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

Hello! i saw some post about replacing nulls or blanks.

I know if i use the "len(trim(field))>0" i can get rid of blanks but i have a lot of tables and a lot of fields so i need another way.

Thx for your help!

Anil_Babu_Samineni — Mon, 03 Jun 2019 13:12:19 GMT

If there are many, I suggest you to work with "ApplyMap" concepts in Qlik.

lbalestieri — Mon, 03 Jun 2019 17:18:37 GMT

In the main() i use this sentence to replace nulls with Empty

Set NullValue = '< Empty >';
NullAsValue *;

What mapping do you suggest on this example below? i mean, if you can (or someone) write the explicit code.

I can select the blank fields but i dont find a way to replace them for a null value or '< Empty >'

Vegar — Mon, 03 Jun 2019 19:00:02 GMT

What is your data source? Is it a text file, excel or inline source? If so then you could use nullinterpret.
E.g. :
set NullInterpret ='';

Read more: https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/ValueHandlingVariables/NullInterpret.htm

Vegar — Mon, 03 Jun 2019 19:06:43 GMT

if you can't use nullintepret then a mapping table could be a good alternative solution, mentioned by @Anil_Babu_Samineni .
If you have a lot of fields that needs mapping I would recommend you not to use the applymap() but instead use
map *fieldlist using MapName

It will allow you to use the map table more efficiently on many fields without writing the applymap(...) for every single field.

Read more here: https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptRegularStatements/Map.htm