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     [CLOSED]Prefix operator skipping missing values?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Prefix operator skipping missing values?
 Login/Join
 
rray9895
Silver Member
posted
Hello guys,

I have a situation that I don't quite understand, and I haven't found anything by searching the documentation. Sometimes when I do a WRITE and the prefix operator FST, if the FST record is missing, it skips it and goes for the next value that has something in it.

So if my data looked like this:

ID Period CODE
1 3 .
1 2 .
1 1 X

and I did FST.CODE, I would get the X instead of the missing value. Is there some sort of way to override this behavior? I've worked around it by creating DEFINE FILES that look like

CODE1/A1 = IF CODE IS MISSING THEN ' ' ELSE CODE;

and calling that on my WRITE command instead of the actual field, but there seems like there should be a better way to do it. Does anyone else experience this behavior, and have a solution?

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 47 | Registered: October 22, 2014Report This Post
Tom Flynn
Expert
posted Hide Post
Sort the data:

BY ID
BY HIGHEST PERIOD

If you did BY HIGHEST 1 PERIOD, wouldn't need FST.


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
rray9895
Silver Member
posted Hide Post
I tried that. I'm doing this in a MATCH (this is key, and probably is the reason none of this is working as expected) I get too many records, even with a BY HIGHEST 1.
This is what the structures of the hold files I'm matching together look like:

(THE TABLE I NEED THE SINGLE HIGHEST RECORD FROM)
ID|PERIOD|CODE
1|3|.
1|2|.
1|1|X

(THE TABLE I'M MATCHING TO)
ID|LASTNAME|FIRSTNAME
1|TEST|BOB



The match logic looks like this:

MATCH FILE HOLD2
WRITE
PERIOD
CODE
BY ID
BY HIGHEST 1 PERIOD
RUN
FILE HOLD1
PRINT *
BY ID
AFTER MATCH HOLD AS HOLD3 NEW
END

When I do a PRINT * to look at the structure it looks like this:

ID|PERIOD|CODE|LASTNAME|FIRSTNAME
1|3|.|TEST|BOB
1|2|.|.|.
1|1|X|.|.


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 47 | Registered: October 22, 2014Report This Post
Tom Flynn
Expert
posted Hide Post
MATCH FILE HOLD2
WRITE
PERIOD
CODE
BY ID
BY HIGHEST 1 PERIOD
RUN
FILE HOLD1
PRINT *
BY ID
AFTER MATCH HOLD AS HOLD3 NEW
END
-RUN
TABLE FILE HOLD3
PRINT *
BY ID
HIGHEST 1 PERIOD
WHERE LASTNAME IS-NOT MISSING;
END
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
rray9895
Silver Member
posted Hide Post
Now if I do this:

MATCH FILE HOLD2
PRINT
PERIOD
CODE
BY ID
BY HIGHEST PERIOD
RUN
FILE HOLD1
PRINT ID
BY ID
AFTER MATCH HOLD AS SORT1 NEW
END

DEFINE FILE SORT1
CODE1/A4 = IF CODE IS MISSING THEN ' ' ELSE CODE;
END

MATCH FILE SORT1
WRITE
FST.CODE1
FST.PERIOD
BY ID
RUN
FILE HOLD1
PRINT *
BY ID
AFTER MATCH HOLD AS HOLD3 NEW
END

Then the output is like this:

ID|PERIOD|CODE|LASTNAME|FIRSTNAME
1|3|.|TEST|BOB

Which is what I want, but it seems like a wonky workaround more than the actual approach I should be taking.

Edit: Let me try that Tom. I'm still having to do an extra hold after my match, but I don't have to create a DEFINE FILE so that is probably a better method.

Double Edit: Alright that works. Now I realize that it isn't the extra hold that is important, it is the WHERE clause where you are excluding missing LASTNAME.

This message has been edited. Last edited by: rray9895,


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 47 | Registered: October 22, 2014Report This Post
Tom Flynn
Expert
posted Hide Post
FYI, After a MATCH, you do usually have to do 1 more HOLD to get to the data you want...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Waz
Expert
posted Hide Post
Another option that may work for you is to work out which record to keep.

e.g. [untested]
TABLE FILE {file}
SUM
COMPUTE Cntr/I9 = IF ID NE LAST ID THEN 1 ELSE LAST Cntr + 1 ;
CODE
BY ID 
BY Period
WHERE TOTAL Cntr EQ 1
END


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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Prefix operator skipping missing values?

Copyright © 1996-2020 Information Builders