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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
NODATA with MATCH
 Login/Join
 
Kamesh
Master
posted
Have two tables and Matching it with the option of OLD-OR-NEW .

I need the output has to have N/A if some of the fields dont have data.

EX:
Table1
Name Id Dept
EMP1 1 CSE
EMP2 2 CSE
EMP3 3 ECE

Table2
Id DESCRIPTION
1 COMPUTER SCIENCE
2
3 ELECTRONICS

MATCH FILE TABLE1
PRINT NAME DEPT
BY ID
RUN
FILE
PRINT DESCRIPTION
BY ID
AFTER MATCH HOLD OLD-OR-NEW
END

TABLE FILE HOLD
PRINT NAME ID DEPT DESCRIPTION
END

The final HOLD file has to look like,

NAME ID DEPT DESCRIPTION
EMP1 1 CSE COMPUTER SCIENCE
EMP2 2 CSE N/A
EMP3 3 ECE ELECTRONICS
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
k.lane
Platinum Member
posted Hide Post
If the data is missing, why not just set:

SET NODATA = 'N/A'
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
reFOCUSing
Guru
posted Hide Post
Could you create defined field that checks the value in the dataset?

DEFINE FILE filename
definefield/A20 = IF fieldname IS MISSING THEN 'N/A' ELSE fieldname;
END

I'm guessing SET NODATA = 'N/A' did not work for you.
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
Kamesh
Master
posted Hide Post
Yes CurtisA - You are right.

SET NODATA='N/A' - not working.
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Kamesh
Master
posted Hide Post
CurtisA - I'm not able to use DEFINE bcoz those fields are decimal fields.
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
k.lane
Platinum Member
posted Hide Post
Just a couple of questions:

Is this just sample data you're using or the real thing? If it's real, then what does the ID represent? Would that be department ID? Employee ID?

I'm confused as to why SET NODATA = 'N/A' isn't working. I tried the same type of report using similar data at my site and nodata worked fine.

Is there a reason why you're using MATCH instead of JOIN in this case?
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
Kamesh
Master
posted Hide Post
that's the sample data.

The reason I'm using MATCH is, I need all the records in TABLE1 and the corresponding records from TABLE2.

Sometimes TABLE2 may have zero records.
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
k.lane
Platinum Member
posted Hide Post
If you need all the records from Table1 then JOIN should work for you. If you need to, SET ALL=PASS.

SET ALL = PASS
JOIN CLEAR *
JOIN ID IN TABLE1 TO ID IN TABLE2

I would expect you to use MATCH if there are records in table2 that are not in table1.
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
Kamesh
Master
posted Hide Post
tried JOIN, still NODATA didn't work.

The field I'm trying to get N/A is Padded fields. Do you think it may be the problem?
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
k.lane
Platinum Member
posted Hide Post
Padded fields?
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
Kamesh
Master
posted Hide Post
Decimal fields
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
TerryW
Silver Member
posted Hide Post
OK, since you have a Decimal field, but want to print an Alpha value, then use a DEFINE with an EDIT, very similar to what CurtisA suggested earlier.

DEFINE FILE filename
alphafield/A20 = IF decimalfield IS MISSING THEN 'N/A' ELSE EDIT(decimalfield);
END
 
Posts: 40 | Registered: March 10, 2004Report This Post
Kamesh
Master
posted Hide Post
I want to display a decimal value with '$' sign. If I use EDIT, then it will truncate decimal part of it.
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
TerryW
Silver Member
posted Hide Post
How about adding the '$' via concatenation?

DEFINE FILE filename
alphafield/A20 = IF decimalfield IS MISSING THEN 'N/A' ELSE '$' | EDIT(decimalfield);
END
 
Posts: 40 | Registered: March 10, 2004Report This Post
TerryW
Silver Member
posted Hide Post
Oops, sorry. I was Focus'ing in (pun intended) on the '$' and not the 'truncate decimal part'. So try this instead:


DEFINE FILE filename
alphafield/A20 = IF decimalfield IS MISSING THEN 'N/A' ELSE FTOA(decimalfield, '(D12.2M)', 'A14');
END
 
Posts: 40 | Registered: March 10, 2004Report This Post
Kamesh
Master
posted Hide Post
The problem is, when MATCH or JOIN two tables. It is not identifying the MISSING data, it simply says 0 for those.
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
TerryW
Silver Member
posted Hide Post
If it has a 0, then change the words "IS MISSING" to "EQ 0" in the DEFINE.

This seems to be way harder than it should be. Are we mis-understanding? Maybe we need more of the code causing the problem, with the actual output versus the expected output.
 
Posts: 40 | Registered: March 10, 2004Report This Post
<JG>
posted
Got to ask the obvious question.
What are the data sources? relational DB, Focus, Flat file.
And is the column in question declared as MISSING ON in the master file.
 
Report This Post
k.lane
Platinum Member
posted Hide Post
Another obvious question. Does the field you are checking for missing contain spaces or a 0 or is it in fact missing?
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
k.lane
Platinum Member
posted Hide Post
Something I forgot to ask. In your first posting, you indicated that your data looked something like the following:

Name Id Dept
EMP1 1 CSE
EMP2 2 CSE
EMP3 3 ECE

Table2
Id DESCRIPTION
1 COMPUTER SCIENCE
2
3 ELECTRONICS

...with the report showing N/A under the description. However you've also indicated that you're checking a decimal field. Are you checking for ID?
 
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders