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] Best way to compare dates when 1 is missing

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Best way to compare dates when 1 is missing
 Login/Join
 
GaryB
Member
posted
I am trying to compare two files with dates in them. Something like this:
MATCH FILE FILE1
PRINT
DATE1
BY KEY
RUN
FILE FILE1A
PRINT DATE1A
BY KEY
AFTER MATCH HOLD OLD-OR-NEW
END
DEFINE FILE HOLD
CMP1/A1 = IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';
END
TABLE FILE HOLD
PRINT
DATE1
DATE1A
CMP1
BY KEY
END

Since the date is missing in 1 file I get this error:

(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED

If both dates are missing the CMP1 value should be "Y".

What is the best way to compare the dates when one or both may be MISSING?

Gary

This message has been edited. Last edited by: GaryB,
 
Posts: 29 | Location: Seattle Washington | Registered: July 08, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
You're not getting the error because a date is missing, you're getting the error because the formats of FILE1.DATE1 and FILE1.DATE1A are not the same, the code fails in this line:

CMP1/A1 = IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';


In my opinion, it is unusual to MATCH data in the same file, so perhaps describing what you're attempting to do might help us come up with suggestions.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Waz
Expert
posted Hide Post
Testing equality with missing data can also be fraught with danger.

May be a good idea to test to see if each field IS MISSING.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
OPALTOSH
Platinum Member
posted Hide Post
Why do you need to use MATCH for this? Both fields are in the same file?
Just do the DEFINE and TABLE on FILE1 and make sure the dates are in the same format, if not then do a DEFINE on one of them to make it the same format as the other one!
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
<JG>
posted
Simple rule,

Unless you are trying to do something very, very clever
Which in this case you are not, JOIN/MATCH is not required.

The single file contains ALL data, It's a simple WHERE test.

As has been mentioned, the error would seem to be because of format miss match.
 
Report This Post
GaryB
Member
posted Hide Post
I am matching data from one system to another. In this case and SAP table, and ClickSchedule. There is an interface from ClickSchedule that updates SAP, but it does not always work. I need a report that checks the values in both SAP and ClickSchedule. There are two date fields, and three text fields that are compared. In some cases SAP is missing the record entirely. I get the error when the date is MISSING (Yes it is really MISSING) in the hold file.

The date fields (and text fields) in my "real" are all the same format.

I typed my example from scratch, because the CAR database doens't have a date fields.

Gary
 
Posts: 29 | Location: Seattle Washington | Registered: July 08, 2009Report This Post
GaryB
Member
posted Hide Post
The second File in my example should be FILE2 or something. I typed it wrong.

BTW, are there Focus sample db's that have date fields?
 
Posts: 29 | Location: Seattle Washington | Registered: July 08, 2009Report This Post
GaryB
Member
posted Hide Post
Also, when I extract the data from SAP and ClickSchedule, it is saved as a Focus DB.
 
Posts: 29 | Location: Seattle Washington | Registered: July 08, 2009Report This Post
PBax
Gold member
posted Hide Post
jobhist & salhist have ymd dates in them.

I would be double checking my formats in the databases....then double checking the format after the match. There must be something different by definition of that error.


81.05 All formats
 
Posts: 56 | Location: Manchester | Registered: November 21, 2006Report This Post
GaryB
Member
posted Hide Post
Changing the define to this eliminates the error:

CMP1/A1 MISSING ON = IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';

I had to test the missing values to get the a value when the data is missing.

CMP1/A1 = IF DATE1 IS MISSING AND DATE1A IS MISSING THEN 'Y' ELSE
IF DATE1 IS MISSING THEN 'N' ELSE
IF DATE1A IS MISSING THEN 'N' ELSE
IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';

G
 
Posts: 29 | Location: Seattle Washington | Registered: July 08, 2009Report This Post
GaryB
Member
posted Hide Post
quote:
CMP1/A1 = IF DATE1 IS MISSING AND DATE1A IS MISSING THEN 'Y' ELSE
IF DATE1 IS MISSING THEN 'N' ELSE
IF DATE1A IS MISSING THEN 'N' ELSE
IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N'


CMP1/A1 = IF DATE1 IS MISSING THEN 'N' ELSE
IF DATE1A IS MISSING THEN 'N' ELSE
IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';

The first test made no sense in my case. It can't happen.
 
Posts: 29 | Location: Seattle Washington | Registered: July 08, 2009Report 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] Best way to compare dates when 1 is missing

Copyright © 1996-2020 Information Builders