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] Alpha Field Contains Data that Needs to Be Broken Out to Alpha and Numeric

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Alpha Field Contains Data that Needs to Be Broken Out to Alpha and Numeric
 Login/Join
 
AMC2
Platinum Member
posted
I have an alpha field(FIELDX) below that contains data such as below:

FIELDX:
30%
checkmark symbol
7244.50

I am trying to come up with logic to display the 30% and the checkmark symbol as they are and to show the 7,244.60 as $7,245. The data values reflecting as numeric (7,244.60) needs to display as a dollar amount.

I am trying to convert to a dollar amount and I need to handle for separating out the the percentage and checkmark data values.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
MartinY
Virtuoso
posted Hide Post
If you are 100% sure that the only differentiator is the % sign, then playing around with something such as this may work
Important lines are FMT and FLD9

DEFINE FILE CAR
FLD1 /A10 = '30%';
FLD2 /A10 = '7244.60';
FLDX /A10 = IF COUNTRY IN ('ENGLAND', 'ITALY') THEN FLD1  ELSE FLD2;
FMT  /A10 = IF COUNTRY IN ('ENGLAND', 'ITALY') THEN 'P3C%' ELSE 'P8.2CM';
FLD9 /P8.2 = EDIT(REPLACE(FLDX, '%', ''));
END
TABLE FILE CAR
SUM FLDX
    FLD9/FMT
BY COUNTRY
END


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
AMC2
Platinum Member
posted Hide Post
Thanks Martin. This seems to be a very good technique. I am missing something though. The % sign is the differentiator for the percentages, but there are a checkmark symbol and dollar amounts as data values for the AMT_FIELD. I'm not sure how to completely handle for the checkmark and I'm not getting the dollar amounts to come out. Here is what I have applied here with your technique:

FLDX/A20 = IF (AMT_FIELD CONTAINS '%' OR '&|#x2714') THEN AMT_FIELD;
FMT/A20 = IF (AMT_FIELD CONTAINS '%' OR '&|#x2714') THEN 'P3C%' ELSE 'P12.2CM';
FLD9/P12.2 = EDIT(REPLACE(FLDX, '%', ''));


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
MartinY
Virtuoso
posted Hide Post
As far as I understand what you are trying to do it certainly not working.
You need to distinguish the field which contain a percentage value than the one with amount.

Must better look like this
-* To detect which output format it should be
FMT/A20 = IF SRC_FIELD CONTAINS '%' THEN 'P3C%' ELSE 'P12.2CM';
-* To remove control character and convert as numeric field
FLD9/P12.2 = EDIT(REPLACE(REPLACE(SRC_FIELD, '&|#x2714', ''), '%', ''));


Also, please use the code tag when posting sample code and/or result
It is the last icon on the ribbon that looks like the below
</>


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
AMC2
Platinum Member
posted Hide Post
I forgot to use the code tag when I posted the last sample code...my apologies.

I don't believe I was clear about the checkmark character...I need to keep it to display it also.

So this modified logic:
  
FMT/A20 = IF AMT_FIELD CONTAINS '%' THEN 'P3C%' ELSE 'P12.2CM';
FLD9/P12.2 = EDIT(REPLACE(REPLACE(AMT_FIELD, '&|#x2714', ''), '%', ''));



Gives this below:

30% is displayed as 30.00
amounts are displayed as .00


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
MartinY
Virtuoso
posted Hide Post
You haven't follow my sample

You should PRINT/SUM FLD9 using FMT format, not to print as is defined

TABLE FILE abc
SUM FLD9/FMT
BY xyz
END


Assuming that you want to print the check mark with the amount and at its right, then :

DEFINE FILE CAR
-* Below three defines are just to simulate input data
FLD1 /A20 = '30%';
FLD2 /A20 = '7244.60' | ' ' | '&|#x2714';
FLDX /A20 = IF COUNTRY IN ('ENGLAND', 'ITALY') THEN FLD1  ELSE FLD2;

FLDC /A20    = IF FLDX CONTAINS '%' THEN FLDX ELSE REPLACE(REPLACE(FLDX, '&|#x2714', ''), '%', '');
FLDN /P8.2CM = IF FLDC CONTAINS '%' THEN 0    ELSE EDIT(FLDC);
FLDP /A30V   = IF FLDC CONTAINS '%' THEN FLDX ELSE FPRINT(FLDN, 'P8.2CM', 'A15') | ' ' | '&|#x2714';
END
TABLE FILE CAR
SUM FLDX AS 'Simulated,Input,Data'
    FLDP AS 'Formated,Displayed,Data'
BY COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
    COLUMN=FLDP,
    JUSTIFY=RIGHT,
$
ENDSTYLE
END

Be advise that other solutions may exist


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
AMC2
Platinum Member
posted Hide Post
Ok, that concatenates the amount and the checkmark. I need the checkmark on it's own row in the output. This is what I tried below with your car sample to get the checkmark on it's own row. However for the checkmark, I get $.00.

FLD1 /A20 = '30%';
FLD2 /A20 = '7244.60';
FLD3/A20 = '&|#x2714';
FLDX /A20 = IF COUNTRY IN ('ENGLAND', 'ITALY') THEN FLD1 ELSE IF COUNTRY IN ('JAPAN') THEN FLD2 ELSE FLD3;

FLDC /A20 = IF FLDX CONTAINS '%' THEN FLDX ELSE REPLACE(REPLACE(FLDX, '&|#x2714', ''), '%', '');
FLDN /P8.2CM = IF FLDC CONTAINS '%' THEN 0 ELSE EDIT(FLDC);
FLDP /A30V = IF FLDC CONTAINS '%' THEN FLDX ELSE IF FLDC CONTAINS '&|#x2714' THEN FLDX ELSE FPRINT(FLDN, 'P8.2CM', 'A15');
END
TABLE FILE CAR
SUM FLDX AS 'Simulated,Input,Data'
FLDP AS 'Formated,Displayed,Data'
BY COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
COLUMN=FLDP,
JUSTIFY=RIGHT,

ENDSTYLE
END


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
MartinY
Virtuoso
posted Hide Post
Come on, pay a little more attention to what your are doing. You could have found the solution yourself

Now it seems that in the same input field, you could have up to three different values that you want to be printable in a proper format and not a combination of them
1- a percentage
2- a check mark
3- a dollars amount

DEFINE FILE CAR
FLD1 /A20 = '30%';
FLD2 /A20 = '7244.60';
FLD3 /A20 = '&|#x2714';
FLDX /A20 = IF COUNTRY IN ('ENGLAND', 'ITALY') THEN FLD1 ELSE IF COUNTRY IN ('JAPAN') THEN FLD2 ELSE FLD3;

FLDN /P8.2CM = IF FLDX CONTAINS '%' OR '&|#x2714' THEN 0    ELSE EDIT(FLDX);
FLDP /A30V   = IF FLDX CONTAINS '%' OR '&|#x2714' THEN FLDX ELSE FPRINT(FLDN, 'P8.2CM', 'A15');
END
TABLE FILE CAR
SUM FLDX AS 'Simulated,Input,Data'
    FLDP AS 'Formated,Displayed,Data'
BY COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
  COLUMN=FLDP,
  JUSTIFY=RIGHT,
$
ENDSTYLE
END


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
AMC2
Platinum Member
posted Hide Post
Ah...that did get pass me. Ok that works for your CAR example. However, I'm still missing something when applying to my data below:

FLDN /P8.2CM = IF AMT_FIELD CONTAINS '%' OR '&|#x2714' THEN 0  ELSE EDIT(AMT_FIELD);
FLDP /A30V   = IF AMT_FIELD CONTAINS '%' OR '&|#x2714' THEN AMT_FIELD ELSE FPRINT(FLDN, 'P8.2CM', 'A15');


I get the checkmarks and percentages where they should be with this, but the amounts are coming out to be $.00 again.


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
AMC2
Platinum Member
posted Hide Post
Actually, I'm getting one row of the dollar amount fields to populate with the currency symbol, but my other two rows dollar amounts are coming out as $.00. The row that is populating with the amounts represent the difference between the first two rows of amounts that are coming out as $.00.


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
AMC2
Platinum Member
posted Hide Post
Am I missing something here?


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
AMC2

Post the entire latest code please..

This message has been edited. Last edited by: FP Mod Chuck,


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
AMC2
Platinum Member
posted Hide Post
Chuck - I actually just resolved this by reformatting my source field before manipulating it. So all data is generating as expected now.

Thanks for following up and thanks to Martin for his help here.


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report 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] Alpha Field Contains Data that Needs to Be Broken Out to Alpha and Numeric

Copyright © 1996-2020 Information Builders