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

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

bwisealiahmad — Thu, 30 Jun 2016 19:57:04 GMT

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.

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

MarcoWedel — Thu, 30 Jun 2016 20:25:57 GMT

If(Field, Field)

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

bwisealiahmad — Thu, 30 Jun 2016 20:28:37 GMT

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

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

MarcoWedel — Thu, 30 Jun 2016 20:41:44 GMT

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

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

trdandamudi — Thu, 30 Jun 2016 20:44:26 GMT

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...

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

bwisealiahmad — Thu, 30 Jun 2016 21:22:00 GMT

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

Mvh,

Ali A

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

andydietler — Thu, 30 Jun 2016 21:26:39 GMT

More explicitly you could write:

LOAD

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

...

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

sunny_talwar — Thu, 30 Jun 2016 21:56:45 GMT

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

If(Field > 0, Field) as Field