3 Replies Latest reply: Sep 22, 2012 4:01 PM by Robert Szarek RSS

    How to handle the null values in the script

    Robert Szarek

      Hi,

       

      I am loading the data via OLE DB from MS Access Views. the Views containd basic components of net sales calculation like price, discounts, volume.. the Net sales calculation for each row is done in the script by preceading Load statement, so the synthax of my script is:

       

      Load

      Net_Price,

      Volume,

      Discount1,

      Discount2,

      Product,

      /other descriptive dimensons/,

      Net_Price * Volume * (1-Discount1) *(1-Discount2) as Net_Sales

       

      SQL Select *

      From MyAccessView

       

      the problem is that Discounts take sometimes null values and for such records, Net_Sales is as well null what is wrong, as this null should have been treated in the formula as zero... I handled this in discount definition as follows:

       

      if(Discount1='',0,Discount1) as Discount__1

       

      The problem is that I can not use Discount__1 name when defining Net_Sales, I can incorporate if... formulas in the Net_Sales definition, but having around 10 kinds of discpounts, it is going to be a looooong formula. Can I handle this differently?

      I was trying to handle this in Acces Views by usnig NZ(Discount1,0) formula, but then QV does not see the Views any more which as I got to know is because it does not recognise NZ() function :-((

       

      Can you please help?