Replacing Blank values with static value

[dmxmikey]

I have a script that outputs txt file and some of the fields are blank and need to have a static value on the output, and was wondering if you can have one piece of code that will replace all blank fields with the static value? so in below example in sub-group all will have a static value and supp-code 959 and  p-status to have a static value. 

supp-code	com-grp	sub-group	p-status
959	100
959	100		A
1723	991		A
91	54		A
889	242		A
1133	104		A
705	677		A

[Vegar]

Does is have a value (is it selectable?) Or is it NULL?

If it is a NULL value them you could use NullAsValue to get a static value.

A good place to start is to look at this page: https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/nu...

[bharathadde · Accepted solution]

you can do it in two ways

On the script end

Create Sub-group column as below

if(Len(Sub-group)>0,Sub-group,'Static Value') as Sub-group

Similarly for p-status

if(Len(p-status)>0,p-status,'Static Value') as p-status

On a Straight table or Pivot table (User interface side

you can use it as dimension or expression

if(Len(Sub-group)>0,Sub-group,'Static Value')

Similarly for p-status

if(Len(p-status)>0,p-status,'Static Value')

[dmxmikey]

Do I need to define the code for every field?

[Vegar]

Not if you're able use Qlik's null handling features, then our is possible to implement the and default value for all null values.

In the other approach that @bharathadde presented, you will need to address every field. The benefit from this approach is that your can customize the default value for each field.

[bharathadde]

Hi @Vegar good to know Qlik's null handling feature. 

But I'm wondering this feature only work for null values but not blank values. Sometimes Blanks are not considered as null values in qlikview, for that case len(field) works well.

[Vegar]

If you are loading data from excel or text files you can change which value that should be interpreted as null by adjusting the NULLINTERPRET variable.

set NullInterpret =''; //will return NULL values for blank values in Excel.


https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ValueHandl...

[bharathadde]

Good to know, you are awesome @Vegar