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     [SOLVED]Parameterized report with duplicate records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Parameterized report with duplicate records
 Login/Join
 
<d3nis370>
posted
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 appreciated

This message has been edited. Last edited by: <d3nis370>,
 
Report This Post
Expert
posted Hide Post
As you have not posted your code, we can only guess what is happening.

My initial guess would be that you have a join or you are tabling from a multi-segment file.

Posting your code will help.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
<d3nis370>
posted

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' );
 
Report This Post
Expert
posted Hide Post
So your report just prints the results of a join.

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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
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.


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report This Post
<d3nis370>
posted
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 );


Thanks for the help!
 
Report 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     [SOLVED]Parameterized report with duplicate records

Copyright © 1996-2020 Information Builders