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]Concatenating alpha fields where some fields are missing

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Concatenating alpha fields where some fields are missing
 Login/Join
 
Trudy
Platinum Member
posted
I have fields for each day of the week and I want to concatenate these fields into one field. I tried in the metadata to create a define with the following syntax
MEETDAYS=SSRMEET_SUN_DAY|SSRMEET_MON_DAY|SSRMEET_TUE_DAY


The Results return a blank field when the SSRMEET_SUN_DAY field is missing, which is almost always. Each field is a A1V type and the MEETDAYS field is an A7V field. When I change the concatenation to the following

MEETDAYS=SSRMEET_MON_DAY|SSRMEET_TUE_DAY


I get results when there is a value in the two fields but no values when there is only a value in one of the fields. How do I code this to ignore those fields that are missing?

Thanks Trudy

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


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
MartinY
Virtuoso
posted Hide Post
Try

MEETDAYS/A3V=SSRMEET_SUN_DAY || SSRMEET_MON_DAY || SSRMEET_TUE_DAY;


The single pipe "|" keeps spaces where the double one "||" remove trailing spaces from left field.
Meaning remove trailing spaces from field 1 before concatenating field 2. But leading spaces from field 2 will be kept.

In your case it should work because trailing spaces from field 2 (in it's case it's only spaces) will be removed because it's followed by "||" to concatenate with a field 3.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Trudy
Platinum Member
posted Hide Post
Martin, thanks for this and yes it does work. I guess I was trying to do this in the metadata so that when I use this table it will always be in the correct format. I cannot seem to get this to work in the metadata but can by using a compute or define in a report. Which I guess I'll have to do for every report that uses these fields.


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Trudy
Platinum Member
posted Hide Post
I figured it out. You need to change the MISSING property in the DEFINE field to 'ON - missing only if all missable operands are missing'

Thanks T


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Shingles
Platinum Member
posted Hide Post
Uggh... the simplest things take way too long...

So looks like I'm trying to do the same as Trudy up there, but I not successful.

I am trying to create a DEFINE in the metadata (synonym) that contcatenates a few fields, but seems like things go to poop when there is null data.

I've tried | and || and all three options for MISSING (ya I'm at the point where I'm just trying all combinations). I can't seem to figure this out. Should the data type be A250 or A250V, what's the difference?

I would really like to do this in the metadata to reap its obvious advantages over DEFINES in the fex.


WebFOCUS 8201, SP 0.1, Windows 7, HTML
 
Posts: 190 | Registered: May 19, 2017Report This Post
Mike in DeLand
Guru
posted Hide Post
I've had decent luck using the DBEXPR function in the metadata - using that, you can use the NVL function of SQL to help handle null values. Also, using this function will avoid the issue of some webfocus functions that don't translate to SQL, requiring a full download of the selected data instead of getting the advantage of aggregation in sql.


Webfocus 8
Windows, Linux
 
Posts: 258 | Location: Palm Coast, FL | Registered: February 05, 2010Report This Post
jgelona
Master
posted Hide Post
Shingles, you should refer to the Creating Reports With WebFOCUS Language manual. See chapter 13 in the 8.2.04 manual. What you say is happening sounds like a bug unless your synonyms are not accurate. The default for a DEFINE is that if the field is computed if SOME of the values are not missing. So in Trudy's original example, I think MEETDAYS should look like this:
MEETDAYS/A3=SSRMEET_SUN_DAY|SSRMEET_MON_DAY|SSRMEET_TUE_DAY;

OR

MEETDAYS/A3 MISSING ON/OFF NEEDS SOME/ALL=SSRMEET_SUN_DAY|SSRMEET_MON_DAY|SSRMEET_TUE_DAY;

With the defaults on, the result should be '.11'. This is how it works for me.

As for the A250 versus A250V, V is a variable length field. For several reasons, probably related to how we have our environment set up, it is just easier to use fixed length fields. We have reports that still run the pre-date WebFOCUS by 10+ years.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 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     [SOLVED]Concatenating alpha fields where some fields are missing

Copyright © 1996-2020 Information Builders