Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Concatenating alpha fields where some fields are missing
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Concatenating alpha fields where some fields are missing
 Login/Join
 
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: 113 | Registered: May 28, 2015Reply With QuoteReport This Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 113 | Registered: May 28, 2015Reply With QuoteReport This Post
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: 113 | Registered: May 28, 2015Reply With QuoteReport This Post
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: 167 | Registered: May 19, 2017Reply With QuoteReport This Post
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: 251 | Location: Palm Coast, FL | Registered: February 05, 2010Reply With QuoteReport This Post
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: 942 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.