Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2010Report 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, 2010Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report 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 


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report 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, 2010Report 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, 2010Report 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, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders