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.
I have a report that gets the user to enter a date range. Once the range is entered it returns the data for that specific range. All the data is correct but for some reason I am getting dupicate records and I don't know why. Any help is appreciatedThis message has been edited. Last edited by: <d3nis370>,
JOIN ACCT AND LOAN_NO IN FDI001 TO MULTIPLE ACCT AND LOAN_NO IN FDI002 TAG J0 AS J0 END DEFINE FILE FDI001 SentVIA/A3=' '; Trk#/A3=' '; DateofDisposition/P9=IF J0.CHECK_STATUS EQ 'P' THEN J0.CK_SENT_DT ELSE J0.PROCESS_DT; I8YYMDFIELD/I8YYMD=DateofDisposition; YYMDofdisposition/YYMD=I8YYMDFIELD; END TABLE FILE FDI001 PRINT 'J0.CK_RCVD_DT' AS 'Date Check ,Received' 'BORROWER_NAME' AS 'Name' 'LOAN_NO' AS 'Loan Number' 'J0.CHECK_AMT' AS 'Amount' 'J0.INS_CO' AS 'Issuer' 'J0.FDI002.DateofDisposition' COMPUTE DaysOutstanding/P9 = DateofDisposition - J0.CK_RCVD_DT; AS 'Days Outstanding' SentVIA AS 'Sent VIA' Trk# 'J0.CHECK_NO' AS 'Check Number' HEADING "Check Log (Endorse)" FOOTING "" WHERE ( ACCT EQ 7125 OR 7126 OR 7127 ) AND ( J0.CHECK_STATUS EQ 'E' ) AND ( J0.YYMDofdisposition GE '&FromDate' ) AND ( J0.YYMDofdisposition LE '&ToDate' );
I would expect that there are multiple record in the FI002 table.
You can resolve this be adding BY fields and changing to a SUM, but beware that a sum will affect numeric fields, you may have to prefix them with a FST, LST or MAX, if you do not want them totaled.
As Waz asked. It will be helpful if you can tell us little about your report. You are using a one to many join.
You should look into you data if you dont have multiple records of your data, you shouldnt get multiple. If you have multiple records of your data in database, you need to use some sort of logic to get unique records. For example, SELECT DISTINCT * FROM TABLE in SQL.
This is what I changed and it got rid of the duplicate records.
JOIN FDI001.FDI001.FDI001_ACCT AND FDI001.FDI001.FDI001_LOAN_NO IN FDI001 TO MULTIPLE FDI002.FDI002.FDI002_ACCT AND FDI002.FDI002.FDI002_LOAN_NO IN FDI002 TAG J0 AS J0 END DEFINE FILE FDI001 SentVIA/A3=' '; Trk#/A3=' '; DateofDisposition/P9=IF J0.FDI002.FDI002_CHECK_STATUS EQ 'P' THEN J0.FDI002.FDI002_CK_SENT_DT ELSE J0.FDI002.FDI002_PROCESS_DT; I8YYMDFIELD/I8YYMD=DateofDisposition; YYMDofdisposition/YYMD=I8YYMDFIELD; daysoutstanding/P9=DateofDisposition - J0.FDI002.FDI002_CK_RCVD_DT; END TABLE FILE FDI001 SUM 'FST.FDI001.FDI001.FDI001_LOAN_NO' AS 'Loan Number' 'FST.J0.FDI002.FDI002_CHECK_AMT' AS 'Amount' 'FST.J0.FDI002.FDI002_INS_CO' AS 'Issuer' 'FST.J0.FDI002.DateofDisposition' AS 'FST,J0.FDI002.DateofDisposition' SentVIA AS 'Sent VIA' Trk# 'FST.J0.FDI002.daysoutstanding' AS 'FST,J0.FDI002.daysoutstanding' BY 'J0.FDI002.FDI002_CHECK_NO' BY 'FDI001.FDI001.FDI001_BORROWER_NAME' BY 'J0.FDI002.FDI002_CK_RCVD_DT' AS 'Date Check ,Received' HEADING "Check Log (Endorse)" FOOTING "" WHERE ( FDI001.FDI001.FDI001_ACCT EQ 7125 OR 7126 OR 7127 ) AND ( J0.FDI002.FDI002_CHECK_STATUS EQ 'E' ) AND ( J0.FDI002.YYMDofdisposition GE '&FromDate' ) AND ( J0.FDI002.YYMDofdisposition LE '&ToDate' ) AND ( J0.FDI002.FDI002_CHECK_NO EQ J0.FDI002.FDI002_CHECK_NO );
Focal Point Forums 