[CLOSED] Filter to last date, which is not greater than other date

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, 2015

Ignored post by BabakNYC posted

Platinum Member

posted

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, 2017

Ignored post by jenni posted

Virtuoso

posted

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, 2013

Ignored post by MartinY posted

pav

Platinum Member

posted

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, 2016

Ignored post by pav posted

Platinum Member

posted

@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, 2017

Ignored post by jenni posted

Platinum Member

posted

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, 2017

Ignored post by jenni posted

Virtuoso

posted

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, 2013

Ignored post by MartinY posted

Platinum Member

posted

@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, 2017

Ignored post by jenni posted

Virtuoso

posted

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, 2013

Ignored post by MartinY posted

Platinum Member

posted

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, 2017

Ignored post by jenni posted

Virtuoso

posted

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, 2013

Ignored post by MartinY posted