Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

bwisealiahmad
Not applicable

Remove 0 and replace with <NULL> (the DB null, not a string)

Hi,

I have a client who sometimes register the wrong amounts and goes in and corrects it to a 0. The issue is that I am using min date and because there is a 0 the date that is connected to that 0 value becomes the lowest date, not the actual real first payment.

Is there any way I can in my script create an IF statement or there is a function to turn something into a NULL so that it get registered as a "nothing"?

Thanks.

7 Replies
MarcoWedel
Not applicable

Re: Remove 0 and replace with <NULL> (the DB null, not a string)

If(Field, Field)

bwisealiahmad
Not applicable

Re: Remove 0 and replace with <NULL> (the DB null, not a string)

I am confused? Did you miss something or is that all?

MarcoWedel
Not applicable

Re: Remove 0 and replace with <NULL> (the DB null, not a string)

That's all.

All numeric values other than 0 are evaluated as true, so all of them will be loaded, all others (0, text or null) will be loaded as null.

hope this helps

regards

Marco

trdandamudi
Not applicable

Re: Remove 0 and replace with <NULL> (the DB null, not a string)

Something like below, I just used Marco code and applied here:

Data:

Load

   If(Daily_Date,Daily_Date) as Daily_Date,

   Department;

LOAD * INLINE [

Daily_Date,Department

0,Division1

06/02/2016,Division2

06/03/2016,Division3

06/04/2016,Division4

];

Hope this helps...

bwisealiahmad
Not applicable

Re: Remove 0 and replace with <NULL> (the DB null, not a string)

I tried it in Qlik Sense and did not work. Will try again.

Mvh,

Ali A

andydietler
Not applicable

Re: Remove 0 and replace with <NULL> (the DB null, not a string)

More explicitly you could write:

LOAD

    If(Num(DateField) > 0, DateField, Null()) AS DateField

...

sunny_talwar
Not applicable

Re: Remove 0 and replace with <NULL> (the DB null, not a string)

Can be tried without Num() as well....

If(Field > 0, Field) as Field