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,

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.

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?

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