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     [CLOSED] Doing Counts on joined tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Doing Counts on joined tables
 Login/Join
 
gco7156
Member
posted
Hi there,

I am using a master file with 3 tables left joined together. The ACCOUNT table being on top and then LEFT JOINED by AccountNumber to both SAVINGS and LOANS.

ACCOUNT --> SAVINGS
ACCOUNT --> LOANS

The fields are as follows:
ACCOUNT.AccountNumber
ACCOUNT.OpenDate
SAVINGS.AccountNumber
SAVINGS.OpenDate
LOANS.AccountNumber
LOANS.OpenDate

When I create a define to get the CNT of Accounts and # Savings and Loans that are opened within 30 days of the Account Opening. The CNT does not seem to be accurate. Or if I am just counting the # of Savings and Loans. My Define is below.

30Day/D12S=
IF ( (SAVINGS.OpenDate FROM ACCOUNT.OpenDate TO DATEMOV ( DATEADD ( ACCOUNT.OpenDate , 'M' , 1 ) , 'EOM' ))
OR
(LOANS.OpenDate FROM ACCOUNT.OpenDate TO DATEMOV ( DATEADD ( ACCOUNT.OpenDate , 'M' , 1 ) , 'EOM' )))
THEN 1 ELSE 0

If I only have 1 entry in Savings and 2 in Loans, then Savings will match Loans and will become 2 as well. So instead of having only total of 3, I get 4.

Can you please tell me how to fix this problem?
Thank you
Glen

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 15 | Registered: January 24, 2020Report This Post
gco7156
Member
posted Hide Post
I am able to work around this issue by creating a view in SQL, doing a UNION for SAVINGS and LOANS and then using the view in webfocus. But that may not be the ideal way to do it. I want to be able to do everything in WebFocus.
 
Posts: 15 | Registered: January 24, 2020Report This Post
jltz83od
Member
posted Hide Post
I've had intermittent success with using joined fields in a DEFINE statement. My understanding is that COMPUTE will work on the result set from the join, which is may be less efficient in some ways but more straight-forward. The is also the issue of the join itself. Are you using LEFT_OUTER to UNIQUE, or something else? Can you post the join?


WebFOCUS 8.2.06, Windows
 
Posts: 22 | Registered: July 18, 2019Report This Post
gco7156
Member
posted Hide Post
Hi jltz83od,

I have not had any luck yet. Support suggested i create 2 separate hold files for SAVINGS and LOANS and then join the totals. I have not tried it yet. So i am still currently using the view i created in SQL that is doing a UNION ALL for both SAVINGS AND LOANS.

Thanks
Glen
 
Posts: 15 | Registered: January 24, 2020Report 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     [CLOSED] Doing Counts on joined tables

Copyright © 1996-2020 Information Builders