topic Re: Replacing Blank values with static value in QlikView

Replacing Blank values with static value

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

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

Re: Replacing Blank values with static value

Vegar — Wed, 20 Mar 2019 15:40:21 GMT

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/null-value-handling.htm

Re: Replacing Blank values with static value

bharathadde — Wed, 20 Mar 2019 15:45:44 GMT

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')

Re: Replacing Blank values with static value

dmxmikey — Wed, 20 Mar 2019 17:17:21 GMT

Do I need to define the code for every field?

Re: Replacing Blank values with static value

Vegar — Wed, 20 Mar 2019 18:14:46 GMT

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.

Re: Replacing Blank values with static value

bharathadde — Wed, 20 Mar 2019 18:33:27 GMT

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.

Re: Replacing Blank values with static value

Vegar — Wed, 20 Mar 2019 18:48:15 GMT

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/ValueHandlingVariables/NullInterpret.htm

Re: Replacing Blank values with static value

bharathadde — Wed, 20 Mar 2019 19:04:18 GMT

Good to know, you are awesome @Vegar