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.
New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.
What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.
During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.
Read-Only TopicGo
Search
Notify
Admin
New PM!
Master posted May 05, 2005 05:30 PM
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, 2005
IP
Platinum Member If the data is missing, why not just set: SET NODATA = 'N/A'
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005
IP
Guru 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.
Master Yes CurtisA - You are right. SET NODATA='N/A' - not working.
Posts: 780 | Location: Florida | Registered: January 09, 2005
IP
Master CurtisA - I'm not able to use DEFINE bcoz those fields are decimal fields.
Posts: 780 | Location: Florida | Registered: January 09, 2005
IP
Platinum Member 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, 2005
IP
Master 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, 2005
IP
Platinum Member 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, 2005
IP
Master 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, 2005
IP
Platinum Member Padded fields?
Posts: 177 | Location: Calgary, Alberta, Canada | Registered: April 25, 2005
IP
Master Decimal fields
Posts: 780 | Location: Florida | Registered: January 09, 2005
IP
Silver Member 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
Master 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, 2005
IP
Silver Member How about adding the '$' via concatenation? DEFINE FILE filename alphafield/A20 = IF decimalfield IS MISSING THEN 'N/A' ELSE '$' | EDIT(decimalfield); END
Silver Member 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
Master 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, 2005
IP
Silver Member 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.
<JG> posted May 07, 2005 07:21 AM
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.
Platinum Member 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, 2005
IP
Platinum Member 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, 2005
IP
Please Wait. Your request is being processed...
Read-Only TopicCopyright © 1996-2020 Information Builders