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     retrieve data from a previous record...(RESOLVED)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
retrieve data from a previous record...(RESOLVED)
 Login/Join
 
ERINP
Platinum Member
posted
I am having coders block. I have a scenario that I can not seem to figure out what I need to do: I have 4 records, each have a transaction#, sorted by highest (4, 3, 2, 1), if trans#2 has a specific DEFINEd value in it, then I need to retrieve a date value from trans#3 and return that date to the row that contains trans#2, this is Excel or html output. Any ideas or suggestions?

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
RSquared
Guru
posted Hide Post
You can use the "LAST" function.
Example
DEFINE CARRY_OVER/HMDYY=
IF LAST DEFINED_FIELD EQ 'I NEED THEN LAST DATE_NEEDED;


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
ERINP
Platinum Member
posted Hide Post
RSquared I will give it a try...seems to make sense and hopefully that works
thanks,
Erin


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
ERINP
Platinum Member
posted Hide Post
RSquared..I attempted your approach to resolve my issue but when I use the 'LAST' in the DEFINE it does not return any data. I tried the following:
1) CARRY_OVER/MDYY = IF LAST DEFINED_FIELD EQ 'Y' THEN LAST DATE_NEEDED; returns no data to CARRY_OVER.
2) CARRY_OVER/MDYY = IF DEFINED_FIELD EQ 'Y' THEN LAST DATE_NEEDED; returns no data to CARRY_OVER
3) CARRY_OVER/MDYY = IF LAST DEFINED_FIELD EQ 'Y' THEN DATE_NEEDED; returns no data to CARRY_OVER
4) CARRY_OVER/MDYY = IF DEFINED_FIELD EQ 'Y' THEN DATE_NEEDED; returns the date but it is for the record that contains the 'Y'.
I'm not sure why the LAST funvtion does not work for any of the 3 attepts that I tried...??...


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
Francis Mariani
Expert
posted Hide Post
Don't you need an ELSE in those IF statements, and the data must be previously sorted in the correct order.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
ERINP
Platinum Member
posted Hide Post
Francis,
those are four seperate attempts to retrieve data from a previous record not four consecutive lines of code therefore the ELSE would not be required. Unless you mean to make them a blank if the conditionis not true like ELSE ''; at the end?
the data is sorted by highest transaction number (4, 3, 2, 1). in my report trans#2 has a DEFINEd value EQ 'Y' if this value EQ 'Y' then I need to retrieve a date value from trans#3 to display it in the row of data for trans#2. But when I apply RSquared logic to my program the LAST function does not return any data. What am I missing here??
Thanks,
Erin


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
ERINP
Platinum Member
posted Hide Post
Francis and RSquared,
Upon further attempts and adding the ELSE '' logic to my code I was able to retrieve the proper data. I used the following code to retrieve data from another record:
CARRY_OVER/MDYY = IF DEFINED_FIELD EQ 'Y' THEN LAST DATE_NEEDED ELSE '';
In my example above this returns a date value from trans#3 to the data for trans#2 when the DEFINEd field has a value EQ 'Y'.
I believe the lack of the ELSE logic was the reason the LAST function was not working previously. Thank you for helping me with my coders block.

Erin


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
Waz
Expert
posted Hide Post
With an IF THEN ELSE, you MUST always code the ELSE.


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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     retrieve data from a previous record...(RESOLVED)

Copyright © 1996-2020 Information Builders