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.

Not applicable

maths: columns A - B when B has no value

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?

1 Solution

Accepted Solutions
amit_saini
Not applicable

Re: maths: columns A - B when B has no value

Hi ,

Please see the attachment.

Thanks,

AS

7 Replies
crusader_
Not applicable

Re: maths: columns A - B when B has no value

Hi,

Try without ' '

if(IsNull(ForecastAmt), 0, ForecastAmt)

Kind regards,

Andrei

tresesco
Not applicable

Re: maths: columns A - B when B has no value

For Difference column try:

=RangeSum( [ActualAmount], -[ForecastAmt])

jontydkpi
Not applicable

Re: maths: columns A - B when B has no value

Or

[ActualAmount] - Alt([ForecastAmt]), 0)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
amit_saini
Not applicable

Re: maths: columns A - B when B has no value

Hi ,

Please see the attachment.

Thanks,

AS

celambarasan
Not applicable

Re: maths: columns A - B when B has no value

'-' 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)


satyadev_j
Not applicable

Re: maths: columns A - B when B has no value

Hi,

Try adding below two lines before your load script,

NullAsValue ActualAmount, ForecastAmt;

Set NullValue = 0;

Satya

Not applicable

Re: maths: columns A - B when B has no value

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.