Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have search various use of Null values but none can fix my issue. Hope someone knows this:
I have:
column A from tblActuals
column B from tblForecasts
when I do a join, I get this (they are correct except when there is a "nothing"):
ActualAmount ForecastAmt Difference
100 120 -20
160 - - <--I want ForecastAmt to show 0, and Difference to show 160
103 150 -47
I've tried:
if(Len(ForecastAmt) > 0, len(ForecastAmt), '0') --- but I still get a '-'
if(IsNull(ForecastAmt), '0', ForecastAmt) --- I also get a '-'
Does anyone know what's wrong with it?
Hi,
Try without ' '
if(IsNull(ForecastAmt), 0, ForecastAmt)
Kind regards,
Andrei
For Difference column try:
=RangeSum( [ActualAmount], -[ForecastAmt])
Or
[ActualAmount] - Alt([ForecastAmt]), 0)
Hi ,
Please see the attachment.
Thanks,
AS
'-' says that the value is null. any mathematical operation with null gives you null
I suggest you to use Alt or RangeSum or NumSum in those cases
=ActualAmount - Alt(ForecastAmt, 0)
or
=RangeSum(ActualAmount,-ForecastAmt)
or
=NumSum(ActualAmount,-ForecastAmt)
Hi,
Try adding below two lines before your load script,
NullAsValue ActualAmount, ForecastAmt;
Set NullValue = 0;
Satya
Thanks everyone,
After I did a resident load of the same table (because I still got a '-' no matter which suggestion I tried), then all of the methods suggested worked.
I'm still not sure why it didn't work though. (I used a Table Box object to see the data)
I would mark all of you as correct if I could. Thanks so much.