Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Defines: Conditional MISSING Confusion
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Defines: Conditional MISSING Confusion
 Login/Join
 
Member
posted
I'm having a problem with how MISSING (aka Null) values are being handled out of a conditional statement. My situation in specific deals with masterfiles, but can be illustrated with the example below. Consider the following, where NULLABLENUMBER supports MISSING:

SET NODATA = 'n/a'

DEFINE FILE TEST
test1/P16.3 = IF 0 EQ 0 THEN TEST.TEST.NULLABLENUMBER ELSE 0.0;
test2/P16.3S MISSING ON= IF 0 EQ 0 THEN TEST.TEST.NULLABLENUMBER ELSE 0.0;
test3/P16.3S MISSING ON=IF 0 EQ 0 THEN (IF TEST.TEST.NULLABLENUMBER EQ MISSING THEN MISSING ELSE TEST.TEST.NULLABLENUMBER) ELSE 0.0;
END

TABLE FILE TEST
PRINT
Scenario NULLABLENUMBER test1 test2 test3
ON TABLE PCHOLD FORMAT HTML
END


Results:
-----------------------------------------------------
Scenario NULLABLENUMBER test1 test2 test3
-----------------------------------------------------
NumHasVal 16 16 16 16
NumIsNull n/a .000 n/a
-----------------------------------------------------


In the case that the NULLABLENUMBER is null, I cannot understand the results for test2, where the defined field supports nulls, the source field supports nulls, but the null in the source field (which has to be picked, since 0 EQ 0) is converted to a zero (and then suppressed). There is a distinct difference between null and zero, but it doesn't seem like that is getting the gravity it deserves...

test3 illustrates a way to work around this issue, but it really seems like a bug that test2 doesn't work.

Could anyone explain this behavior?

This message has been edited. Last edited by: Kerry,


Version 7.6.10
Windows 7 Pro
all output
 
Posts: 18 | Registered: May 20, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Apologies - the "Results" table didn't post as cleanly as it should. hopefully this will work.

Results:
-----------------------------------------------------
Scenario        NULLABLENUMBER  test1   test2   test3
-----------------------------------------------------
NumHasVal          16              16      16      16
NumIsNull            n/a             .000            n/a
-----------------------------------------------------


Version 7.6.10
Windows 7 Pro
all output
 
Posts: 18 | Registered: May 20, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
My assumption is that the field edit option is coming into play, S, which will suppress zero.

Why I don't know.

What happens when you take the S off ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6207 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
you might want to try

testx/P15 MISSING ON NEEDS ALL DATA


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
For what it's worth, in 7.7.01 this repro on CAR file seems to produce the results you were expecting:
 TABLE FILE CAR
SUM RPM BY CAR
    ACROSS COUNTRY
ON TABLE SET HOLDMISS ON
ON TABLE SET ASNAMES FOCUS
ON TABLE HOLD FORMAT FOCUS
END
SET NODATA = 'n/a'
DEFINE FILE HOLD
test1/P16.3             = IF 0 EQ 0 THEN RPMENGLAND ELSE 0.0;
test2/P16.3S MISSING ON = IF 0 EQ 0 THEN RPMENGLAND ELSE 0.0;
test3/P16.3S MISSING ON = IF 0 EQ 0 THEN (IF RPMENGLAND EQ MISSING THEN MISSING ELSE RPMENGLAND) ELSE 0.0;
END
TABLE FILE HOLD
PRINT CAR RPMENGLAND test1 test2 test3
ON TABLE PCHOLD FORMAT HTML
END 


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 147 | Registered: October 19, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Waz - thanks for the response. I've added test4 to this, to test your idea:

test4/P16.3 MISSING ON= IF 0 EQ 0 THEN TEST.TEST.NULLABLENUMBER ELSE 0.0;

the results are as follows:
Results:
----------------------------------------------------------------------
Scenario        NULLABLENUMBER  test1   test2   test3   test4
----------------------------------------------------------------------
NumHasVal     16                         16        16       16       16
NumIsNull       n/a                        .000                n/a     .000
----------------------------------------------------------------------


Version 7.6.10
Windows 7 Pro
all output
 
Posts: 18 | Registered: May 20, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Dave - thanks for your suggestion as well. I've tried test5/P16.3 MISSING ON NEEDS ALL DATA= IF 0 EQ 0 THEN TEST.TEST.NULLABLENUMBER ELSE 0.0;

Results:
----------------------------------------------------------------------
Scenario        NULLABLENUMBER  test1   test2   test3   test4   test5
----------------------------------------------------------------------
NumHasVal     16                         16        16       16       16        16
NumIsNull       n/a                        .000                n/a     .000      n/a
----------------------------------------------------------------------



This seems to work in the define. I'll see if I can get it to work in my master file.


Version 7.6.10
Windows 7 Pro
all output
 
Posts: 18 | Registered: May 20, 2010Reply With QuoteReport This Post
Member
posted Hide Post
Fixed. Checking the ALL box in the master file definition resolved this. Thanks to everyone on the forum that contributed their ideas - it really helped lead to a solution


Version 7.6.10
Windows 7 Pro
all output
 
Posts: 18 | Registered: May 20, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Defines: Conditional MISSING Confusion

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.