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 not recognized as a Filter in Info Assist

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Missing not recognized as a Filter in Info Assist
 Login/Join
 
Member
posted
I have joined a Fact Table (Policy) to a Bridge table (Group Policies). Since I have records in the Fact Table that are not in the Bridge Table, I have done a Left Outer Join Unique. I also have a Dimension Table (Group Dim) Left Outer Join Unique to the Bridge Table. My Group Dim fields are all set Missing = ON. I am also inner joining to a Month Dim (Snapshot Month Key) once and a Data Dim twice (Pol Eff Date and Pol Exp Date).

I am now trying to view in the Info Assist canvas Active Policies and their Premium that are not associated to a Group.

I initially set my report as follows:
Row By = Policy Number and Group Account (unselect "Hide Missing" Display Option on each)
Measure = Policy Premium (unselect "Hide Missing" Display Option)
Filter = Where Snapshot Month Equal and a Grouped Where "AND" Statement for Pol Eff Date and Pol Exp Date

When I run this, I get all records from the Fact with the Group Account displaying values and as missing ("-") accordingly.

Here is where the problem occurs:
If I apply the filter "Group Account Not Missing" the Run correctly returns only records with a Group Account.
However, when I change the filter to "Group Account Missing" and Run, the report returns with no records.

What am I doing wrong that is preventing the missing Group Account records from being returned using the Missing Filter?

Thank you for your help.

This message has been edited. Last edited by: FP Mod Chuck,


Application Edition
SQL Server
 
Posts: 14 | Registered: June 13, 2017Report This Post
Virtuoso
posted Hide Post
Can you test your code by running it with SQL Trace and post the results from both with and without the filter?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
SQL Trace result for Missing

FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: POL_TAGGI
NG_SUPPORT_BRG
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
(FOC2689) AGGREGATION DONE ...
SELECT
T1."POL_N",
T6."GRP_CD",
SUM(T1."TTL_WRT_PREM_AMT")
FROM
ipfowner.dbo.POL_PREM_LN_MTH_SNP_F T1,
ipfowner.dbo.MTH_DIM T2,
ipfowner.dbo.POL_TAGGING_SUPPORT_BRG T5,
IPFOWNER.dbo.GRP_DIM T6,
ipfowner.dbo.DT_DIM T16,
ipfowner.dbo.DT_DIM T17
WHERE
(T2."MTH_KEY" = T1."MTH_KEY") AND
(T5."POL_CNTRCT_ID" = T1."POL_CNTRCT_ID") AND
(T6."GRP_KEY" = T5."GRP_KEY") AND
(T16."DT_KEY" = T1."POL_TERM_EXP_DT_KEY") AND
(T17."DT_KEY" = T1."POL_TERM_EFF_DT_KEY") AND
(T2."YR_MTH" = '201905') AND
(T2."YR_MTH" >= '197501') AND
(T6."GRP_CD" IS NULL) AND
(T16."DT" > CAST( '20190514' AS DATE)) AND
(T17."DT" <= CAST[ '20190514' AS DATE))
GROUP BY
T1."POL_N",
T6."GRP_CD"
ORDER BY
T1."POL_N",
T6."GRP_CD";
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 HOLDING HTML FILE ON PC DISK ...



SQL Trace result for Not Missing

FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: POL_TAGGI
NG_SUPPORT_BRG
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
(FOC2689) AGGREGATION DONE ...
SELECT
T1."POL_N",
T6."GRP_CD",
SUM(T1."TTL_WRT_PREM_AMT")
FROM
ipfowner.dbo.POL_PREM_LN_MTH_SNP_F T1,
ipfowner.dbo.MTH_DIM T2,
ipfowner.dbo.POL_TAGGING_SUPPORT_BRG T5,
IPFOWNER.dbo.GRP_DIM T6,
ipfowner.dbo.DT_DIM T16,
ipfowner.dbo.DT_DIM T17
WHERE
(T2."MTH_KEY" = T1."MTH_KEY") AND
(T5."POL_CNTRCT_ID" = T1."POL_CNTRCT_ID") AND
(T6."GRP_KEY" = T5."GRP_KEY") AND
(T16."DT_KEY" = T1."POL_TERM_EXP_DT_KEY") AND
(T17."DT_KEY" = T1."POL_TERM_EFF_DT_KEY") AND
(T2."YR_MTH" = '201905') AND
(T2."YR_MTH" >= '197501') AND
(T6."GRP_CD" IS NOT NULL) AND
(T16."DT" > CAST( '20190514' AS DATE)) AND
(T17."DT" <= CAST[ '20190514' AS DATE))
GROUP BY
T1."POL_N",
T6."GRP_CD"
ORDER BY
T1."POL_N",
T6."GRP_CD";
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 HOLDING HTML FILE ON PC DISK ...



I also tried to Filter Group Account = _FOC_NULL or _FOC_NONE. Here is the SQL Trace for that

FOC2525 - FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: POL_TAGGI
NG_SUPPORT_BRG
FOC2509 - RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
FOC2524 - JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY
(FOC2689) AGGREGATION DONE ...
SELECT
T1."POL_N",
T6."GRP_CD",
SUM(T1."TTL_WRT_PREM_AMT")
FROM
( ( ( ( ( ipfowner.dbo.POL_PREM_LN_MTH_SNP_F T1
INNER JOIN
ipfowner.dbo.MTH_DIM T2
ON (T2."MTH_KEY" = T1."MTH_KEY") )
LEFT OUTER JOIN
ipfowner.dbo.POL_TAGGING_SUPPORT_BRG T5
ON T5."POL_CNTRCT_ID" = T1."POL_CNTRCT_ID" )
LEFT OUTER JOIN
IPFOWNER.dbo.GRP_DIM T6
ON T6."GRP_KEY" = T5."GRP_KEY" )
INNER JOIN
ipfowner.dbo.DT_DIM T16
ON (T16."DT_KEY" = T1."POL_TERM_EXP_DT_KEY") )
INNER JOIN
ipfowner.dbo.DT_DIM T17
ON (T17."DT_KEY" = T1."POL_TERM_EFF_DT_KEY") )
WHERE
(T2."YR_MTH" = '201905') AND
(T2."YR_MTH" >= '197501') AND
(T16."DT" > CAST( '20190514' AS DATE)) AND
(T17."DT" <= CAST[ '20190514' AS DATE))
GROUP BY
T1."POL_N",
T6."GRP_CD"
ORDER BY
T1."POL_N",
T6."GRP_CD";
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 HOLDING HTML FILE ON PC DISK ...

Thanks again


Application Edition
SQL Server
 
Posts: 14 | Registered: June 13, 2017Report This Post
Virtuoso
posted Hide Post
Could YR_MTH be both equal to 201905 AND >= 197501?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
The >=197501 filter is something I added to the Month Dim to eliminate all dates prior to the inception of our company. It is being applied to other reports/charts and is not creating any issue.


Application Edition
SQL Server
 
Posts: 14 | Registered: June 13, 2017Report This Post
Master
posted Hide Post
Looking at your first two SQL Traces, they are using an implicit join between tables, which acts as an INNER JOIN and only brings back all matching records. So there will be no missing values. Try setting your join to be a LEFT_OUTER MULTIPLE or SET SHORTPATH = SQL.

Read more in the docs: Handling a Missing Segment Instance

There was also a really good webinar on joins that you can watch here: FOCUS: JOIN in Depth and FOCUS/XFOCUS Database Internals


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Member
posted Hide Post
Thank you Hallway.

That information was extremely helpful. I had the Left outer join set, but I switched the relation from Unique to Multiple. However, that still did not correct the issue. The implicit join was still defaulting when I added the Not Missing or Missing filter.

I ended up also having to add SET ALL = PASS manually by editing the report with text editor.

Thank you for your help


Application Edition
SQL Server
 
Posts: 14 | Registered: June 13, 2017Report This Post
Master
posted Hide Post
That's great! I'm so glad that you found a resolution.

Don't forget to prepend '[Solved]' to the title of the post


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report 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 not recognized as a Filter in Info Assist

Copyright © 1996-2020 Information Builders