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     Effective Dates and PeopleSoft

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Effective Dates and PeopleSoft
 Login/Join
 
Gold member
posted
Is anyone out there using WebFOCUS with peoplesoft or with effective dates?

I want to know how to handle effective date logic in a report. I can do a sum and take the max but only that data element is the correct one while the rest are the last record in the database matching the other criteria.

Basically I want the below data to show all of the current addresses for each employee ID.

I tried


TABLE FILE SAMPLE_DATES
SUM
EFFDT AS 'MAX,EFFDT'
CITY
STATE
EFF_STATUS
BY
EMPLID

SAMPLE_DATES data...
EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Elmhurst IL A
00000000001 1/1/1900 Chicago IL I
00000000002 1/1/1900 St. Louis MO I
00000000002 1/1/2000 Madison WI A
00000000003 1/1/1900 Seatle WA I
00000000003 1/1/2000 Portland OR A
Should return

EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Elmhurst IL A
00000000002 1/1/2000 Madison WI A
00000000003 1/1/2000 Portland OR A
Instead it returns

EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Chicago IL I
00000000002 1/1/2000 Madison WI A
00000000003 1/1/2000 Portland OR A
Note that the effective dates are all correct but the other data is not from the same row as the max effdt. How would I do this?

In this particular case I could use EFF_STATUS but this isn't always an option. Can anyone help me?

This message has been edited. Last edited by: <Mabel>,
 
Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004Report This Post
Expert
posted Hide Post
George, if you sort your db by date first, then the sum function will bring you the proper values across the board.
or even the LST.EFFDT LST.CITY etc if you prefer, makes your code more readable.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
George,

The short answer is that WebFOCUS for PeopleSoft (WFPS) provides techniques for handling PS effective dated tables. If you do not already have WFPS, I recommend that you contact your local IBI rep; and they will put you in touch with the folks who can give you more details.

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
Guru
posted Hide Post
Try using a PRINT and

BY EMPLOYEE_ID
BY HIGHEST 1 EFFDT

This will sort them in descending order by date and limit it to the first occurrence in the group.

Be sure to include WHERE statements that will return only valid active rows (ie. make sure they have the right status and aren't future dated).
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
<Mabel>
posted
quote:
Originally posted by George Brown:
Is anyone out there using WebFOCUS with peoplesoft or with effective dates?

I want to know how to handle effective date logic in a report. I can do a sum and take the max but only that data element is the correct one while the rest are the last record in the database matching the other criteria.

Basically I want the below data to show all of the current addresses for each employee ID.

I tried

TABLE FILE SAMPLE_DATES
SUM
EFFDT AS 'MAX,EFFDT'
CITY
STATE
EFF_STATUS
BY
EMPLID
SAMPLE_DATES data...
EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Elmhurst IL A
00000000001 1/1/1900 Chicago IL I
00000000002 1/1/1900 St. Louis MO I
00000000002 1/1/2000 Madison WI A
00000000003 1/1/1900 Seatle WA I
00000000003 1/1/2000 Portland OR A
Should return


EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Elmhurst IL A
00000000002 1/1/2000 Madison WI A
00000000003 1/1/2000 Portland OR A

Instead it returns


EMPLID EFFDT CITY STATE EFF_STATUS
00000000001 1/1/2000 Chicago IL I
00000000002 1/1/2000 Madison WI A
00000000003 1/1/2000 Portland OR A
Note that the effective dates are all correct but the other data is not from the same row as the max effdt. How would I do this?

In this particular case I could use EFF_STATUS but this isn't always an option. Can anyone help me?
 
Report This Post
Member
posted Hide Post
George,

The code I use for finding the most recent record is as follows(I'll use your field names):

TABLE FILE SAMPLE_DATES
SUM
MAX.EFF_DT
BY EMPLID
PRINT CITY STATE EFF_STATUS
BY EMPLID
WHERE TOTAL EFF_DT EQ MAX.EFF_DT
END


See if that'll work for you. It works on our system but it's not peoplesoft.


**EDIT Forgot the by phrase.
Fletcher

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


Production/Test: WF 7.1.4, WIN2K3 Server
 
Posts: 12 | Location: Wilson, NC | Registered: November 14, 2005Report 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     Effective Dates and PeopleSoft

Copyright © 1996-2020 Information Builders