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

[bwisealiahmad]

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.

[MarcoWedel]

If(Field, Field)

[bwisealiahmad]

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

[MarcoWedel]

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]

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]

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

Mvh,

Ali A

[andydietler]

More explicitly you could write:

LOAD

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

...

[sunny_talwar]

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

If(Field > 0, Field) as Field