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] missing / null data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] missing / null data
 Login/Join
 
Member
posted
We want a null or missing condition rather than a zero.

Here is the code:
SET ALL = PASS
JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING
END
-*
DEFINE FILE EMPDATA
HDATE/MDYY MISSING ON = DATECVT(HIREDATE,'A8YYMD','MDYY');
SDATE/MDYY MISSING ON = DATECVT(COURSESTART,'A8YYMD','MDYY');
DAYS/D9.1 MISSING ON NEEDS ALL DATA = DATEDIF(SDATE,HIREDATE,'D');
END
-*
TABLE FILE EMPDATA
PRINT PIN DEPT HIREDATE
COURSESTART COURSECODE DAYS
ON TABLE HOLD AS HOLD1
END
-*
TABLE FILE HOLD1
COMPUTE AVE.DAYS
BY DEPT
END

Here is the result:
DESIRED
RESULT
PIN DEPT HIREDATE PIN COURSESTART COURSECODE DAYS
000000010 MARKETING 7/12/1989 000000010 9/18/1989 PDR740 68 68
000000020 MARKETING 7/18/1990 -33072 .
000000030 SALES 4/11/1990 000000030 NAMA730 . .
000000030 SALES 4/11/1990 000000030 5/30/1990 EDP090 49 49
000000040 MARKETING 5/1/1990 000000040 5/13/1990 EDP750 12 12
000000050 SALES 3/15/1989 000000050 5/22/1989 UMI720 68 68
000000060 MARKETING 3/1/1989 -32568 .
000000070 ACCOUNTING 3/5/1990 -32937 .
000000080 MARKETING 2/13/1991 000000080 6/19/1991 EDP690 126 126
000000080 MARKETING 2/13/1991 000000080 2/14/1991 BIT420 1 1


For pin #30 coursecode #NAMA730 we are getting the null condition correctly since a record does exist in the child table. However, for pin #20, since there is no child record the cooursestart is "populating" with a zero giving us an incorrect DAYS difference.
How can we get the zero to be a null so that our DAYS calculate properly?

This message has been edited. Last edited by: <Kathryn Henning>,


TEST: WF 8.0.9 - DevStudio 8.0.9
PROD: WF 8.0.9 - DevStudio 8.0.9
Platform: SQL Server/Windows
 
Posts: 11 | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
I don't think I understand correctly what exactly you need. The sample output you attached is also very hard to read due to the font size being used. Please edit your post and enclose your code and "desired result" in CODE tags.

Anyway, run this code and analyze the resulting data:

SET ALL = PASS
JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING
END
-*
DEFINE FILE EMPDATA
HDATE/MDYY MISSING ON = DATECVT(HIREDATE,'A8YYMD','MDYY');
SDATE/MDYY MISSING ON = DATECVT(COURSESTART,'A8YYMD','MDYY');
DAYS/D9.1 MISSING ON NEEDS ALL DATA = DATEDIF(SDATE,HIREDATE,'D');
END
-*
TABLE FILE EMPDATA
PRINT
        EMPDATA.PIN AS 'E_PIN'
        TRAINING.PIN AS 'T_PIN'
        DEPT
        HIREDATE 
        COURSESTART
        COURSECODE
        DAYS
WHERE EMPDATA.PIN EQ '0000000$$'
END


Short-path (missing child record) entries are highlighted in the output and I can see that both COURSESTART and DAYS have a correct missing value in those cases. Where exactly are you seeing a "zero" value instead?




Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Member
posted Hide Post
This has been solved.
We believe this was happening because the source table was SQL. When we saved the data to a focus hold table and then did the join the date fields populated with blanks properly.


TEST: WF 8.0.9 - DevStudio 8.0.9
PROD: WF 8.0.9 - DevStudio 8.0.9
Platform: SQL Server/Windows
 
Posts: 11 | Registered: October 31, 2006Report 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] missing / null data

Copyright © 1996-2020 Information Builders