Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Uploading all "0" as '' (blank field)

Dear Community,

is there somehow a chance to set all to be uploaded data that is = 0 as ' ' (blank field) in the script?

My current workaround is to:

    if(@4=0,'',@4) as Jan,

    if(@5=0,'',@5) as Feb,

    if(@6=0,'',@6) as Mae,

... which obviously can't be the solution. 😉

Thanks in advance and best regards,

donuteater

8 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     As of me making 0 to '' blank field in single code is not possible.

     You can do it by checking like your approach only.

    

     if(@4=0,'',@4) as Jan,

    if(@5=0,'',@5) as Feb,

    if(@6=0,'',@6) as Mae,

this one.

Celambarasan

jagan
Luminary Alumni
Luminary Alumni

Hi,

You can try

if(@4=0, null(),@4) as Jan,

if(@5=0,null(),@5) as Feb,

if(@6=0,null(),@6) as Mae,

Regards,

Jagan.

Not applicable
Author

Hm, this is bad.

The problem is, that my lineal regression is totally useless, since i've only got values for the first 4 months. May to December are taking into the calculation with a zero, which gives a straight downward trend.

Dou you perhaps have any other ideas how to fix this issue?

// it can't be solved by uploading a null()-value ... 😕

swuehl
MVP
MVP

donuteater,

you could probably handle the zero values in your expressions, I think your issue is somewhat similar to the discussion John had here:

http://community.qlik.com/message/103596

Maybe I am wrong though..

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You could use a mapping table.

ZeroMap:

MAPPING LOAD 0, '' AutoGenerate 1;

MAP Jan USING ZeroMap;

MAP Feb USING ZeroMap;

etc

-Rob

http://robwunderlich.com

swuehl
MVP
MVP

Good idea, Rob!

Instead of defining each field mapping in a separate statement, I believe we could also use

MAP Jan, Feb, Mae USING ZeroMap;

or even

MAP * USING ZeroMap;

right?

Not applicable
Author

Thanks for the reply:

Could you please explain, what the single command lines do?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Good suggestion Stefan. Yes, the syntax can be simplified by using a field list or wildcard.

The individual MAP lines cause the ZeroMap to be applied to the specified fields. ZeroMap maps an "0" into "".

-Rob