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     [SOLVED] DEFINE field comparing dates, IF...ELSE... and Loops

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] DEFINE field comparing dates, IF...ELSE... and Loops
 Login/Join
 
Platinum Member
posted
I'm not a FOCUS pro, but I thought this would be fairly simple; however, I just can't seem to hold my mouth right.

Goal: define a field that is equal to the most recent (i.e. closest to today) date field in the table.

Say I have 5 date fields (in my real data I have 50) that are USAGE=MDYY, ACTUAL=DATE:
DATE_1
DATE_2
DATE_3
DATE_4
DATE_5
 


These fields are always sequential: DATE_1 < DATE_2 < DATE_3... Furthermore, it's possible that the last N dates are null [e.g. Dates 1-3 have data, while 4 and 5 are null).

So, starting with the last date field (5 in this case, 50 in my real date) and working backward, I want my DEFINE field to equal the first one that is prior to today.

Here's what I have right now that isn't working (I get an "IF...ELSE...THEN syntax error.")
 DEFINE FILE MY_GREAT_TABLE
	MOST_RECENT_DATE/MDYY WITH DATE_1 = IF MDY(&MDYY, DATE_5) < 0 THEN DATE_5 ELSE 
		IF MDY(&MDYY, DATE_4) < 0 THEN DATE_4 ELSE
		IF MDY(&MDYY, DATE_3) < 0 THEN DATE_3 ELSE
		IF MDY(&MDYY, DATE_2) < 0 THEN DATE_2 ELSE
		IF MDY(&MDYY, DATE_1) < 0 THEN DATE_1 ELSE
		MISSING;
END 


I'm not sure if I can use &MDYY in the function, or if the MDY() function is even the right one to use, and I'm also not sure what I can set in the final ELSE to make the DEFINE null.

This message has been edited. Last edited by: J.Hines,



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Virtuoso
posted Hide Post
As a first step, try the following.
To be able to assign a Null (MISSING) value to a DEFINEd field you must first define the field to be "nullable"
Second you must use "LT" and not "<"
DEFINE FILE MY_GREAT_TABLE
 MOST_RECENT_DATE/MDYY WITH DATE_1 MISSING ON = IF MDY(&MDYY, DATE_5) LT 0 THEN DATE_5 ELSE 
		IF MDY(&MDYY, DATE_4) LT 0 THEN DATE_4 ELSE
		IF MDY(&MDYY, DATE_3) LT 0 THEN DATE_3 ELSE
		IF MDY(&MDYY, DATE_2) LT 0 THEN DATE_2 ELSE
		IF MDY(&MDYY, DATE_1) LT 0 THEN DATE_1 ELSE
		MISSING;
END


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
Virtuoso
posted Hide Post
I'm sure you have a very good reason for this DEFINE. However, depending on the DBMS you're reading and the number of rows in your database, your report may take a lot longer than it should. There's always a temptation to fix data issues with a report. When I run into situations like this, I try to see if there's a better way of deconstructing the data so the users don't have to suffer the wait time. This might be a good case for creating a batch job that creates an extract of the data you'd like using HOLD syntax. If you're talking a couple of hundred rows no big deal, but if the data is going to grow and in a year you're looking at Millions of rows, consider caching the result so your reports don't get blamed for inefficiencies in the data.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
BabakNYC: appreciate the concern, but this DEFINE is within a report that has certain WHERE conditions that, combined with the nature of the data, will result in a couple hundred rows max, and this DEFINE is only applicable for that query, not the table as a whole.



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Platinum Member
posted Hide Post
Martin:
Thanks, that at least gets the report running, but I can't get it to return anything other than zero.

In part, I was making it harder than it needed to be trying to convert and use date diff functions, because both my table date and my current date field are Smart Dates, so I can just use an arithmetic operator, e.g.:
CURR_DATE/MDYY = &MDYY;
MY_DT/MDYY = IF CURR_DATE - DATE_5 GT 0 THEN VEST_DATE_5 ELSE VEST_DATE_1;


However, the problem is that if DATE_5 is blank/null, then it all fails silently and the field gets set to MISSING. I'm guessing this is due to the whole IF statement exiting if one of the dates is null, when It would be better if it just returned 0, or moved on because NULL/MISSING/etc doesn't satisfy the condition "GT 0".

And apparently I can't just do
IF (DATE_5 IS NOT MISSING AND CURR_DATE - DATE_5 GT 0)

because FOCUS doesn't seem to have shortcut exits, and will still evaluate the equality even if DATE_5 is missing!

This message has been edited. Last edited by: J.Hines,



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Platinum Member
posted Hide Post
Okay, think I finally solved it. The important part, in addition to checking for missing, was to have
NEEDS ALL so that the expression would continue.

Here then is my final code (now it just remains to be seen if this still works when I take all 50 date fields into consideration):
DEFINE FILE T_SPS_GRANT_LOAD
  CURR_DATE/MDYY = &MDYY;
  MOST_RECENT_DATE/MDYY WITH DATE_1 MISSING ON NEEDS ALL = 
    IF (DATE_5 NE MISSING) AND (CURR_DATE - DATE_5) GT 0 THEN DATE_5 ELSE 
    IF (DATE_4 NE MISSING) AND (CURR_DATE - DATE_4) GT 0 THEN DATE_4 ELSE
    IF (DATE_3 NE MISSING) AND (CURR_DATE - DATE_3) GT 0 THEN DATE_3 ELSE
    IF (DATE_2 NE MISSING) AND (CURR_DATE - DATE_2) GT 0 THEN DATE_2 ELSE
    IF (DATE_1 NE MISSING) AND (CURR_DATE - DATE_1) GT 0 THEN DATE_1 ELSE
    MISSING;
END



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Virtuoso
posted Hide Post
Maybe use a -REPEAT loop? This sample code should carry the latest date forward to the last xdate field.
DEFINE FILE T_SPS_GRANT_LOAD
-REPEAT ENDREPEAT1 FOR &J FROM 1 TO 50
-SET &I = &J - 1 ;
 XDATE_&J/MDYY = IF (&J = 1) THEN DATE_1 ELSE
                 IF (DATE_&J NE MISSING) THEN DATE_&J ELSE XDATE_&I ;
-ENDREPEAT1
-*
 MOST_RECENT_DATE/MDYY = XDATE_50 ;
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Platinum Member
posted Hide Post
@Dan:
That's very interesting! I had no idea something like that was even possible. I'll give that a whirl too. However, I still have to check whether DATE_&J is before the current date.

EDIT:
I got it working, just had to tweak it a bit and add XDATE_0 as a define, because apparently even though XDATE_&I is in the first ELSE, it still runs that part of the code, and was complaining about it being undefined, and therefore each subsequent loop the XDATE_&I was also undefined.
So here's the code:
-REPEAT ENDREPEAT1 FOR &J FROM 1 TO 50
-SET &I = &J - 1 ;
 XDATE_0/MDYY MISSING ON = MISSING;
 XDATE_&J/MDYY = IF (&J = 1) THEN DATE_1 ELSE
                 IF (DATE_&J NE MISSING AND CURR_DATE - DATE_&J GT 0) THEN DATE_&J ELSE XDATE_&I ;
-ENDREPEAT1

This message has been edited. Last edited by: J.Hines,



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Virtuoso
posted Hide Post
Ah yes....XDATE_0 would need to be previously defined despite the fact that it will never be used. However, by placing the DEFINE for XDATE_0 inside the loop it is being executed 50 times when once is sufficient. I would place the DEFINE for XDATE_0 before the -REPEAT loop.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
Jess,
I think that your loop should be reversed. Quoting you:
"DATE_1 < DATE_2 < DATE_3"
I suggest:
  
CURR_DATE/MDYY='&DATEMDYY';
MOST_RECENT_DATE/MDYY MISSING ON=
-REPEAT #LDATE FOR &I FROM 50 TO 2 STEP -1;
IF (DATE_&I NE MISSING) AND (CURR_DATE LT DATE_&I) THEN DATE_&I ELSE
-#LDATE
DATE_1;


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 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     [SOLVED] DEFINE field comparing dates, IF...ELSE... and Loops

Copyright © 1996-2020 Information Builders