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     [SOLVED]Parameterized report with duplicate records
Go
New
Search
Notify
Tools
Reply
  
[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>,
 
Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport 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' );
 
Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport 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, 2010Reply With QuoteReport 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!
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Parameterized report with duplicate records

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