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:
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.
IF ( (SAVINGS.OpenDate FROM ACCOUNT.OpenDate TO DATEMOV ( DATEADD ( ACCOUNT.OpenDate , 'M' , 1 ) , 'EOM' ))
(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?
GlenThis 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?
WebFOCUS 8.2.06, Windows
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.
|Powered by Social Strata|