Hello. I'd like to preface this question by stating that I am very new to all aspects of WebFOCUS and InfoAssist.

All of the data that I am working with is accessed via Oracle PeopleSoft tables. Most of the tables have multiple records for any given datapoint with an effective date listed to determine the sequence. I was thinking that an effective date should be something that I can filter for, but I haven't determined the correct way to filter for what I am looking for. I feel like this should be a pretty basic aspect of creating reports within InfoAssist, but unfortunately I am not sure where to start.

A more specific example of what I am looking to do: I have tables that list a series of buildings. All buildings have at least one inactive (I) and active (A) row listed in the table. Some have more than one of each. Every row has a date associated with it. Currently when I create a filter Building Status EQ "A" I still receive all of the buildings that have inactive records because they have inherent active records and there is no date specified. How can I setup an effective date where I pull the status for all records as of today's date? How can I set it up to see the status for all building records as of a certain date in the past?

I appreciate any help you can give me.

Evan

This message has been edited. Last edited by: <Emily McAllister>,

If I am understanding you correctly you just need to filter where Building Status is equal to "A" & where the associated date is Greater than or equal to a date based off the current date.

WHERE BUILDINGSTATUS EQ "A"
WHERE STATUSDATE GE &YYMD



The second filter must be an "Expression" to use &YYMD (which just pulls current date).

If you wanted just the date greater than a week ago you could do the same filter with just "- 7" at the end.

WHERE STATUSDATE GE &YYMD - 7

This is all of course to make the report automated, by using &YYMD you can now schedule the report and it will run according to the date it is run. Otherwise you could just use a static date filter.

Being new, I'd recommend checking out the documentation, lots to learn:
http://infocenter.informationb...%2Fsource%2Fjoin.htm

Thank you so much for the quick reply and the useful link at the bottom of the page.

Unfortunately your proposed solution doesn't appear to be working. I had gotten the BUILDINGSTATUS part and that works as expected, but 'WHERE STATUSDATE GE &YYMD' produces 0 records as there are no records with dates for today and the future. I really don't think I explained the situation as clearly as I should have, so I will list a clearer example below.

Bldg 1
10/22/2016 Active
11/13/2016 Inactive

Bldg 2
09/23/2015 Inactive
04/01/2016 Active

Bldg 3
01/12/2016 Inactive
08/01/2016 Active

I am looking for a way to report the max effective dated record as of the day that the report is ran for active buildings. So, if it was ran for today, the second row for Bldg 2 and Bldg 3 would be reported because they have a status of Active, but Bldg 1 would not (and more importantly the Active row that is older than the Inactive row for Bldg 1 would not be reported).

I do appreciate the help very much and please let me know if you have any questions about the way that I am explaining things.

Evan P.,
Use 2 steps to get the report.

1st: Pull your data and hold it with these:
BY BUILDING
BY HIGHEST 1 EFFECTIVE_DATE(Your column name here).
This will only bring in rows with the most recent date.

2nd: TABLE FILE HOLD and use your final filter: WHERE STATUS EQ 'Active';

hth
Tom

Thank you for the reply, Tom. I am very new to InfoAssist, and development in general, and was hoping you or someone else wouldn't mind expanding on a couple of points:

'BY HIGHEST 1 EFFECTIVE_DATE' - how do I go about modifying the field with HIGHEST 1? Should this be built into a calculated field? Is this a type of sort? (Edit: Figured HIGHEST 1 out by opening in text editor. If there is a way to access this from inside InfoAssist GUI, please let me know.)

'TABLE FILE HOLD' - I understand the filter portion of the statement, but I am unfamiliar with this terminology. What exactly are you describing here? can I have an example of how this should read?

Thank you for your time in helping an InfoAssist newbie.

This message has been edited. Last edited by: Evan P.,

Hi Evan,
Sorry, YOU need to acclimate yourself with syntax as WebFOCUS is a language as well as an application.

At the top of the main forum page is a link to Technical Documentation. From there, select the VERSION of WebFOCUS 8; there are many, 8.0.X, 8.1.X and 8.2.X. Just having WebFOCUS 8 in your signature means nothing.
Functions in 8.1.X may not be availble in 8.0.X; please modify your signature accordingly.

TABLE FILE {MASTERFILE_NAME} is a core beginning in WebFOCUS; training would have taught that.

Here is a LINK of the documentation showing a search with multiple words(notice the double quotes).
AND, can't use Tinypic anymore, blocked...

The forum can be searched to assist in increasing your knowledge, training is the best route.

Good Luck!
Tom

To get BY HIGHEST/LOWEST n FIELDNAME in InfoAssist, right click on the field and pick Sort. First pick Ascending or Descending. Then right click Sort and pick Limit. You can pick any of the numbers you see listed or Custom to enter 1.

To HOLD data means, caching your answerset into a a data file instead of the screen. In Infoassist you can do that by clicking FILE under the Home Tab. It will let you name the HOLD file so you can then run a report or chart using it as the source.

Tom,

Thanks for the advice. I have updated my signature to reflect the correct version of WebFOCUS. I've saved the link that you provided and plan to spend a considerable amount of time diving into the WebFOCUS language. I am just trying to work around my lack of knowledge using the GUI in the meantime.

BabakNYC,

Thank you for pointing these areas out within the GUI. This is very helpful information.

If I'm understanding correctly the problem still hasn't been solved, sorting by high to low, holding the data, and then filtering by "Active" will still return buildings that are technically inactive correct?

You need to create a compute field to get this to work correctly.

Sort BY
Building
StatusDate (sort descending as previously mentioned)
BuildingStatus

Now hit the "Compute" button under the "Data" tab:



Name the compute "Flag", change the format to I1, and put this in the body: IF Building EQ LAST Building AND BuildingStatus EQ "Active" THEN 1 ELSE 0



This will put a 1 next to any active field that is technically inactive. (as it's active date is earlier than it's inactive date)

Now Hold the data (as previously mentioned) as temporary & the format as Binary, name the hold as you please.



After hitting save you must also hit "Create Report" down at the bottom of the page:



Sort BY
StatusDate (descending) (If you want to see date)
Building
BuildingStatus

and then filter where BuildingStatus EQ "Active" & Where Flag NE 1



This should return your desired results! Also sorry about the confusion earlier, wasn't understanding you, let me know if the above doesn't work!

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

Cimmerian,

You are correct that there were some records that were still inactive showing on my report. I went through with your proposed solution here and those remaining records have been removed. All records that are being reported are records with Active rows as their most recent row.

This works perfectly. Thank you for the help!