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] Filter to last date, which is not greater than other date

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Filter to last date, which is not greater than other date
 Login/Join
 
Platinum Member
posted
Hi,

I want to create a filter.
I enter my Date. Then, the filter is supposed to look after the date A, which is LE my entered date for each value.
But, the date A is not always filled, sothat I Need to look after the last (highest) date after mine for each value.

How can I do that??

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 177 | Registered: October 17, 2017Report This Post
Virtuoso
posted Hide Post
Define a field that checks for the value of &DATE_A and set it to whatever you want using IF THEN ELSE.

Something like this logic:
DATE_A/YYMD = IF &DATE_A EQ '' THEN DATEADD(MY_DATE, 'D', 1) ELSE &DATE_A;  


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
That's not what I am looking for.

I got a timeline for values, let's say for Models like in the car database, so I got different Groups.
But my timeline is not full, there are some null values.

I want to enter a day by Parameter. When I enter 30.05.2018 for example for Model1, then I want my filter to look for the next available date LE 30.05.2018 for Model1, Model2 etc.

Understand?


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 177 | Registered: October 17, 2017Report This Post
Virtuoso
posted Hide Post
Not clear for me
quote:

to look after the date A, which is LE my entered date for each value.

To look AFTER you should use GT (greater than) not less equal (LE)

quote:

But, the date A is not always filled, so that I need to look after the last (highest) date after mine for each value

What is "after mine" ? Another date parameter ?

quote:

next available date LE 30.05.2018 for

You want the next available record that is less or equal (LE) to the entered date ?


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
If I understand correctly, are you looking for date range or something like that.

for example, if you enter the date like '2018-10-24' in the UI then you want the filter condition


like where date from '2018-10-01' to '2018-10-24';??
 
Posts: 109 | Registered: February 02, 2016Report This Post
Platinum Member
posted Hide Post
@pav: Close to that.
Given Dates for value A are: 20.06.2005; 20.06.2015
Given Dates for value B are: 20.06.2004; 20.06.2005; 20.06.2016

My entered Parameter: 20.07.2016 -> then I want to get the Dates of Value A and B, which are as Close as possible to my date, but smaller:
I want to get then: A: 20.06.2015; B: 20.06.2016.

When I enter: 21.06.2015 -> then: A:20.06.2015; B:20.05.2005 (because 20.06.2016 is greater than my entered date)

And third example:
enter: 19.06.2005 -> A: 20.06.2005; B: 20.06.2004


So I want to get for each value which has multiple Dates to next lower date individually..

Hope it's getting more clear, kind of hard to explain.

Just want LE Date for my entered Parameter date for each value/group


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 177 | Registered: October 17, 2017Report This Post
Platinum Member
posted Hide Post
MAYBE SOMETHING LIKE:

CLOSEST_DATE/DMYY. = IF DATEDIF(&DATE, OTHERDATE, 'D') GE 0 AND DATEDIF() LE LAST DATEDIF() AND VALUE EQ LAST VALUE THEN DATEDIF() ELSE IF DATEDIF(&DATE, OTHERDATE, 'D') GE 0 AND DATEDIF() GE LAST DATEDIF() AND VALUE EQ LAST VALUE THEN LAST DATEDIF()

But I get a fault in Infoassist and can't create the filtercondition... Can someone help??


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 177 | Registered: October 17, 2017Report This Post
Virtuoso
posted Hide Post
Hi jenni,

Difficult to follow you. Except if you did a mistake yourself, your third sample is wrong based on your requirement:
quote:

And third example: enter: 19.06.2005 -> A: 20.06.2005; B: 20.06.2004

A: 20.06.2005 is greater then 19.06.2005 so it should not select any value from A according to this : "I want to get the Dates of Value A and B, which are as Close as possible to my date, but smaller"

quote:

CLOSEST_DATE/DMYY. = IF DATEDIF(&DATE, OTHERDATE, 'D') GE 0 AND DATEDIF() LE LAST DATEDIF() AND VALUE EQ LAST VALUE THEN DATEDIF() ELSE IF DATEDIF(&DATE, OTHERDATE, 'D') GE 0 AND DATEDIF() GE LAST DATEDIF() AND VALUE EQ LAST VALUE THEN LAST DATEDIF()

Where are the parameters/values for the DATEDIF functions ?

Assuming that "I want to get the Dates of Value A and B, which are as Close as possible to my date, but smaller" is the requirement, something such as this should give you the proper result:
TABLE FILE abc
SUM MAX.RECORDDATE
BY SOMETHING
WHERE RECORDDATE LE &DATE;
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
@MartinY

Yes, you're right, I made a mistake by the third example.


I want to enter the Parameter.

The Thing is, that I Need to create a Define which I can include in my businesview, sothat the user can just use it.

So I can't use your Code Example..
I


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 177 | Registered: October 17, 2017Report This Post
Virtuoso
posted Hide Post
quote:

The Thing is, that I Need to create a Define which I can include in my businesview, sothat the user can just use it.

It may be very difficult not to say impossible since you need to have the records ordered by date within each value (A, B) to be able to perform such a DEFINE using LAST key word otherwise you can end up with a comparison of date between record from A and B or C. How knows...

So except if you can confirm 200% that you data (within the TABLE FILE) will always be in proper value/date order (which that I'm pretty sure you can't because you don't know what the user will do) I actually cannot see how you will be able do to such a thing from a DEFINE.

As far as I can think of for now, you either have to do it similar to my sample or in several steps with HOLD file(s) but not directly from a DEFINE in one step.
But I could have another way that I'm not thinking of.
There is so many ways to do stuff in FOCUS...


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
When I would try it in PLSQL with ORACLE, then it's easy:

CREATE TABLE t1(value VARCHAR(2), datum DATE);
CREATE TABLE t2(value VARCHAR(2), datum DATE);

INSERT INTO t1 VALUES ('D', TO_DATE ('01.03.2000', 'DD.MM.YYYY'));
INSERT INTO t1 VALUES ('D', TO_DATE ('20.05.2006', 'DD.MM.YYYY'));
INSERT INTO t1 VALUES ('C', TO_DATE ('15.05.2001', 'DD.MM.YYYY'));
INSERT INTO t1 VALUES ('C', TO_DATE ('10.04.2001', 'DD.MM.YYYY'));
INSERT INTO t1 VALUES ('A', TO_DATE ('21.05.2002', 'DD.MM.YYYY'));
INSERT INTO t1 VALUES ('A', TO_DATE ('19.05.2002', 'DD.MM.YYYY'));

INSERT INTO t2 VALUES ('B', TO_DATE ('20.05.2002', 'DD.MM.YYYY'));
COMMIT;




select
value, datum
from
(select
d.value,
d.datum,
dense_rank() over (partition by d.value order by d.datum desc) as rank
from
t1 d
inner join t2 p on p.datum >= d.datum)
where
rank = 1



But don't know how to Transfer that to WebFOCUS.

Think db_expr would also not be a solution, because then all the Dates would have to be the same...


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 177 | Registered: October 17, 2017Report This Post
Virtuoso
posted Hide Post
quote:

select
value, datum
from
(select
d.value,
d.datum,
dense_rank() over (partition by d.value order by d.datum desc) as rank
from
t1 d
inner join t2 p on p.datum >= d.datum)
where
rank = 1

You are exactly to what I told : you are using a "sub-query/over partition" to have the dates in the proper order to then be able to extract the good one. Which is in a sense two steps.
That you cannot perform with a DEFINE -EXCEPT- if your data is already in the proper order that you cannot confirm because you don't know what the user will do.

Not sure if it can be a solution, but maybe using a Reporting Object may solve the issue.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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] Filter to last date, which is not greater than other date

Copyright © 1996-2020 Information Builders