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     [CLOSED] Doing Counts on joined tables
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Doing Counts on joined tables
 Login/Join
 
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: 14 | Registered: January 24, 2020Reply With QuoteReport This Post
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: 14 | Registered: January 24, 2020Reply With QuoteReport This Post
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: 17 | Registered: July 18, 2019Reply With QuoteReport This Post
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: 14 | Registered: January 24, 2020Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Doing Counts on joined tables

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