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] Put data in a field when the cross referenced row is MISSING

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Put data in a field when the cross referenced row is MISSING
 Login/Join
 
Rick Man
Platinum Member
posted
I am trying to create a field that contains either the data in a field from the cross referenced file or a fixed literal, like 'CA' in the example below. I can kind of do that by using the NODATA character; however, the 'CA' it puts in is different than the 'CA' from the file because when I do a COUNT BY NEW_LOC I get 2 rows. I need 1 with a total of 15.

Stated another way, for missing segments use a literal.

I've tried many variations of SET HOLDMISS and SET ALL

Thanks for your help.

Here's the ouput, part of it:

NEW_LOC COUNT *
COUNT
CA 11
CA 4
CO 1
CT 4 
APP PATH IBISAMP
SET ALL=ON
SET HOLDLIST=PRINTONLY
SET COUNTWIDTH=ON
SET ASNAMES=ON
SET HOLDMISS=ON
SET ALL=PASS
SET NODATA = 'CA'
JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING AS J1
DEFINE FILE EMPDATA
NEW_LOC/A6 WITH GRADE = IF PIN IS MISSING THEN 'CA' ELSE LOCATION;
END
TABLE FILE EMPDATA
PRINT * NEW_LOC 
BY PIN
ON TABLE HOLD FORMAT FOCUS AS NEW_LOC
END
TABLE FILE NEW_LOC
COUNT *
BY NEW_LOC
END

This message has been edited. Last edited by: Rick Man,


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report This Post
Tom Flynn
Expert
posted Hide Post
  
APP PATH IBISAMP
SET ALL=ON
SET HOLDLIST=PRINTONLY
SET COUNTWIDTH=ON
SET ASNAMES=ON
SET HOLDMISS=ON
SET ALL=PASS
SET NODATA = 'CA'
JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING AS J1
DEFINE FILE EMPDATA
END
TABLE FILE EMPDATA
PRINT *  
ON TABLE HOLD FORMAT FOCUS AS NEW_LOC
END
-RUN
DEFINE FILE NEW_LOC
  NEW_LOC/A6 WITH GRADE = IF E01 NE E11 THEN 'CA' ELSE LOCATION;
END
TABLE FILE NEW_LOC
COUNT *
BY NEW_LOC
END
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Rick Man
Platinum Member
posted Hide Post
Awesome. Thanks Tom.
But why does it work?
If I use 2 unique names in the NEW_LOC file it also works. So it doesn't need the alias'


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report This Post
Tom Flynn
Expert
posted Hide Post
Hey Rick,

When you do the initial JOIN, the KEY columns from each file is included in the HOLD file, including, non-JOINed data, because of SET ALL=ON, which is over-ridden with SET ALL=PASS, which is a LEFT-OUTER.

Each of the columns in the HOLD has an alias, generated sequentially from the HOST columns to the JOINed columns.

Then, after you retrieve all the data, do your DEFINE stuff with the alias, which, is what E01 and E11 are, alias names.

You could also do a CONDITIONAL JOIN and use TAGs to differentiate the same column names, different syntax though.

Hope this helps...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Rick Man
Platinum Member
posted Hide Post
Well all that I get. What I don't understand is why I need to create a hold file to do the DEFINE/COMPARE on. This is what I was trying to do and hoped to, because I hate passing data twice.

DEFINE FILE EMPDATA
NEW_LOC/A6 WITH GRADE = IF PIN IS MISSING THEN 'CA' ELSE LOCATION;
END


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report This Post
Tom Flynn
Expert
posted Hide Post
Rick,

Whenever you are including missing data, a HOLD is "usually" required. Data in both files won't match.

Do this:

  
APP PATH IBISAMP
SET ALL=ON
SET HOLDLIST=PRINTONLY
SET COUNTWIDTH=ON
SET ASNAMES=ON
SET HOLDMISS=ON
SET ALL=PASS
SET NODATA = 'CA'
JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING AS J1
DEFINE FILE EMPDATA
END
TABLE FILE EMPDATA
PRINT *  
-* ON TABLE HOLD FORMAT FOCUS AS NEW_LOC
END
-EXIT


Look how the data is organized.
By HOLDing, then DEFINEing, then TABLEing, you address the mismatched records...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Rick Man
Platinum Member
posted Hide Post
Thanks Tom for your help and quick response. I appreciate it.


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report 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] Put data in a field when the cross referenced row is MISSING

Copyright © 1996-2020 Information Builders