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     [CLOSED] DISPLAYING LATEST RECORD - Please help

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] DISPLAYING LATEST RECORD - Please help
 Login/Join
 
Platinum Member
posted
I am trying to retrive records based on the latest time stamp and discard duplicate records. But I am still getting duplicate records back even after using BY HIGHEST. Please, can someone hep out.? Here is my code:

 
TABLE FILE SALES
PRINT
     COMPUTE NS_AMOUNT/D20.2C = ( SALES.SALES_LARGE.S_AMOUNT * SALES.CUR_RATE.RATE ); OVER
     SALES.SALES_LARGE.STOCK_CNT/I6C OVER
     COMPUTE N_OSAMT/D20.2C = ( SALES.SALES_LARGE.OSAMT * SALES.CUR_RATE.RATE ); OVER
     COMPUTE NSTOCK_AMT/D12.2 = ( SALES.SALES_LARGE.STOCK_AMT * SALES.CUR_RATE.RATE ); 

BY  SALES.SALES_LARGE.LOCATION_CD NOPRINT
BY  SALES.SALES_LARGE.STORE_NM
BY  SALES.SALES_LARGE.RTE1_CD
BY  SALES.SALES_LARGE.RTE2_CD
BY  SALES.SALES_LARGE.PLAN
BY  SALES.SALES_LARGE.TYPE_CD
BY  SALES.SALES_LARGE.STORE_NM
BY  SALES.SALES_LARGE.TRANS_DT
BY  HIGHEST 1 SALES.SALES_LARGE.SALES_TIME NOPRINT

WHERE SALES.SALES_LARGE.LOCATION_CD EQ &LOCATION_CD.(OR(FIND SALES.SALES_LARGE.LOCATION_CD IN SALES)).Location:.;
WHERE SALES.SALES_LARGE.STORE_NM EQ &STORE_NM.(OR(FIND SALES.SALES_LARGE.STORE_NM IN SALES)).Store Name:.;
WHERE SALES.SALES_LARGE.PLAN EQ &PLAN.(OR(FIND SALES.SALES_LARGE.PLAN IN SALES)).Plan:.;
WHERE SALES.SALES_LARGE.TYPE_CD EQ &TYPE_CD.(OR(FIND SALES.SALES_LARGE.TYPE_CD IN SALES)).Type:.;
WHERE SALES.SALES_LARGE.CY_CD EQ '&CY_CD.(FIND SALES.SALES_LARGE.CY_CD IN SALES).Cy:.';

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
Platinum Member
posted Hide Post
quote:
BY HIGHEST 1 SALES.SALES_LARGE.SALES_TIME NOPRINT


This shold be your first sort


WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML
 
Posts: 165 | Registered: September 29, 2008Report This Post
Platinum Member
posted Hide Post
Thanks for the suggestion.

I have tried it but it's still bringing back multiple records with same BY FIELDS values.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
Platinum Member
posted Hide Post
umun,
Try using just "BY HIGHEST TimeStamp"
Instead of "BY HIGHEST 1 TimeStamp"

ERINP


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by ERINP:
umun,
Try using just "BY HIGHEST TimeStamp"
Instead of "BY HIGHEST 1 TimeStamp"

ERINP


Tried but no success.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
Platinum Member
posted Hide Post
umun,
Obviously you have created an example using the sales file...can you post the full code you are trying to use? SO I can see if something else within it may be causing this issue. If the first BY field is BY HIGHEST SALES.SALES_LARGE.SALES_TIME it should return the highest time.

I just noticed that you are using PRINT which would return multiple records that have the same value for SALES.SALES_LARGE.SALES_TIME BUT if you were to use a SUM then it would return only one record for the BY HIGHEST SALES.SALES_LARGE.SALES_TIME.

ERINP

This message has been edited. Last edited by: ERINP,


WebFOCUS 7.6.9

Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5
Reporting Server OS/400 V5R4M0
Outputs: HTML, Excel, PDF, CSV, and Flat Files
 
Posts: 130 | Location: Columbus, Ohio | Registered: February 25, 2009Report 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     [CLOSED] DISPLAYING LATEST RECORD - Please help

Copyright © 1996-2020 Information Builders