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] Show Highest, only if the highest is in my date range

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Show Highest, only if the highest is in my date range
 Login/Join
 
Member
posted
Hi everyone!
I'm trying to write a report that will take a user entered date range, grab all the corresponding rows that match the subject but only show the highest row if the highest row matches the date range entered. If the date range could corespond to anther row and there is a higher one the subject should not show at all.

Example:
TABLE FILE CARS
PRINT
NAME AS 'Buyer Name'
MAKE AS 'Car Make'
MODEL AS 'Car Model'
DATE AS 'Purchase Date'
BY DEALSHIP NOPRINT
BY SALESPERSON NOPRINT
BY CALENDARYR NOPRINT
BY NAME NOPRINT

WHERE (STARTDATE GE DT(date1) AND ENDDATE LE DT(date2) AND ENDDATE GE DT(date1)) OR (ENDDATE LE DT(date2) AND ENDDATE GE DT(date1));

Assuming this date range,
date1 = 1/1/2010
date2 = 1/1/2013
I'd get something like this:

_____________
HONDA DEALERSHIP
2010
BOB SALESMAN
MARY BUYER
HONDA | CIVIC | 1/1/2010

HONDA DEALERSHIP
2012
BOB SALESMAN
MARY BUYER
HONDA | ACCORD| 1/1/2012

HONDA DEALERSHIP
2013
BOB SALESMAN
MARY BUYER
HONDA | CIVIC | 1/1/2013
______________

But we only want to show the most recent (the highest) row and disregard everything else no matter what the user enters. Like a "Show me the most recent car purchase from every buyer during whatever date range I put in there" Report. The cherry on top is if there is another row AFTER the end date (like MARY Bought a car 4/5/2013, well after end date 1/1/2013) we don't want anything to show at all in the report. That is where I'm really struggling.

How do we grab all rows for a certain 'thing' then out of those grab the highest row and, finally, determine whether or not we want to show it?

Thank you so much,
Shawna

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.6.7
WIN XP
Excel, PDF, HTML
 
Posts: 25 | Registered: October 05, 2011Report This Post
Virtuoso
posted Hide Post
You have in fact two reports
First select the most recent sold car and hold that record then do the second selection
Combine the two selections to one record set and make a report on that.
Or read the first line into some amper values and use them in a sub foot.
Many ways to solve this.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
Could you use BY HIGHEST 1 to get the most recent and WHERE TOTAL to filter the period. This should give what you require.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Master
posted Hide Post
Wouldn't something like this work?

TABLE FILE etc
PRINT NAME etc
BY HIGHEST 1 DATE NOPRINT
BY ...(other BY cluses)
WHERE DATE GE 20100101
AND DATE LE 20121231;
END


Your WHERE clause looks unnecessarily complex to me ....

You might have to HOLD and rearrange the result of course.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Platinum Member
posted Hide Post
If I understand you correctly, and if the code that you supplied already retrieves the correct records, then have you tried to add BY HIGHEST 1 DATE to your query e.g.
TABLE FILE CARS
PRINT
NAME AS 'Buyer Name'
MAKE AS 'Car Make'
MODEL AS 'Car Model'
DATE AS 'Purchase Date'
BY DEALSHIP NOPRINT
BY SALESPERSON NOPRINT

BY HIGHEST 1 DATE

BY CALENDARYR NOPRINT
BY NAME NOPRINT

WHERE ...... ;


The way WebFOCUS should process your request is that it will first retrieve all the records that match your WHERE clause, and then, within that answer set, it will find the "HIGHEST 1" date per DEALERSHIP/SALESPERSON combination.


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Platinum Member
posted Hide Post
Ah wait - I read your request again.
This won't be very efficient, but a possible approach:
First select the most recent record for every buyer - then hold that data - and then apply your WHERE clause for your date range on the HOLD file
e.g.
TABLE FILE CARS
PRINT all the fields you need
BY NAME
BY HIGHEST 1 DATE
ON TABLE HOLD AS XXX
END
-RUN
TABLE FILE XXX
PRINT whatever you want to see
WHERE selection for date range ;
END
  


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Platinum Member
posted Hide Post
Apologies - I only just saw Alan's earlier post regarding combining "BY HIGHEST 1" with "WHERE TOTAL". That may in fact be a more elegant "one step" solution which should produce the same result as my last suggestion.


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Expert
posted Hide Post
Go Alan, thats the way, one step.

Its important to know the order of processing of a TABLE FILE.

I remember some of the old course notes that has diagrams of the order of processing. I think that these should be readily available so we can pin it to our workstations.

Who can remember the order ?


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Member
posted Hide Post
Why would a WHERE TOTAL work?
I've looked through some documentation and I'm just not seeing how to properly use this. Hmm.

Thanks for all your help!
Shawna


WebFOCUS 7.6.7
WIN XP
Excel, PDF, HTML
 
Posts: 25 | Registered: October 05, 2011Report This Post
Member
posted Hide Post
This Totally WORKED. OMG THANK YOU.
I'm not going to close this just yet though, I'm still interested in the WHERE TOTAL clause.
Thank you again, I owe ya a drink.

quote:
Originally posted by Twanette:
Ah wait - I read your request again.
This won't be very efficient, but a possible approach:
First select the most recent record for every buyer - then hold that data - and then apply your WHERE clause for your date range on the HOLD file
e.g.
TABLE FILE CARS
PRINT all the fields you need
BY NAME
BY HIGHEST 1 DATE
ON TABLE HOLD AS XXX
END
-RUN
TABLE FILE XXX
PRINT whatever you want to see
WHERE selection for date range ;
END
  


WebFOCUS 7.6.7
WIN XP
Excel, PDF, HTML
 
Posts: 25 | Registered: October 05, 2011Report This Post
Virtuoso
posted Hide Post
Shawna

WHERE/IF TOTAL works on the aggregated values, but it is also used to create a final screening condition before report display.

TABLE FILE CARS
PRINT all the fields you need
BY NAME
-* Select top record(s) with the highest date 
BY HIGHEST 1 DATE
-* Limit to including and above the lowest date in the date range selected
WHERE DATE GE 'lowest date'
-* Finally, after everything has been retrieved, limit the result shown to chosen date range
WHERE TOTAL DATE FROM 'lowest date' TO 'highest date'
END


For some reason realised I had missed the TOTAL out of the last where clause! The most important bit...

This message has been edited. Last edited by: Alan B,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Master
posted Hide Post
Frowner Geez, Alan gets all the credit, snif ....


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Member
posted Hide Post
I tried this but experienced some interesting results. It works with 2 BY fields, but as I add more (like 'BY SALESPERSON', etc) I'm no longer only returning 1 row back per the entire report.

I'll keep trying though and thank you very much for your help.

quote:
Originally posted by Alan B:
Shawna

WHERE/IF TOTAL works on the aggregated values, but it is also used to create a final screening condition before report display.

TABLE FILE CARS
PRINT all the fields you need
BY NAME
-* Select top record(s) with the highest date 
BY HIGHEST 1 DATE
-* Limit to including and above the lowest date in the date range selected
WHERE DATE GE 'lowest date'
-* Finally, after everything has been retrieved, limit the result shown to chosen date range
WHERE DATE FROM 'lowest date' TO 'highest date'
END



omg George, I am so sorry! THANK YOU! Smiler
You helped me out too. Every1's suggestion was helpful, I just don't understand how to implement all of the suggestions, haha.
You rock.

quote:
Originally posted by George Patton:
Frowner Geez, Alan gets all the credit, snif ....


WebFOCUS 7.6.7
WIN XP
Excel, PDF, HTML
 
Posts: 25 | Registered: October 05, 2011Report This Post
Member
posted Hide Post
I have a program that is pulling various variables and I asking to pull by the highest 1 effective term (date). It is placed on hold using format alpha. However, at the end of the result file there is a '1'. I do not want it to print. I am using the following code but it is not working. How do I get rid of it?

BY HIGHEST 1 SCBCRSE_EFF_TERM NOPRINT


WebFOCUS 8
Windows, All Outputs
 
Posts: 27 | Registered: January 10, 2017Report This Post
Platinum Member
posted Hide Post
It is holding the rank number in your hold file.

Try adding this ...

ON TABLE SET HOLDLIST PRINTONLY


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Member
posted Hide Post
quote:
ON TABLE SET HOLDLIST PRINTONLY



Hello,
It worked but it is not in a text format.

I need the results to be in text format. With this command, it is printing in html


WebFOCUS 8
Windows, All Outputs
 
Posts: 27 | Registered: January 10, 2017Report 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] Show Highest, only if the highest is in my date range

Copyright © 1996-2020 Information Builders