Focal Point
[SOLVED] Alpha Field Contains Data that Needs to Be Broken Out to Alpha and Numeric

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1157014396

May 14, 2020, 10:16 PM
AMC2
[SOLVED] Alpha Field Contains Data that Needs to Be Broken Out to Alpha and Numeric
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
May 15, 2020, 07:56 AM
MartinY
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
May 15, 2020, 10:55 AM
AMC2
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
May 15, 2020, 11:26 AM
MartinY
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
May 15, 2020, 12:10 PM
AMC2
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
May 15, 2020, 01:43 PM
MartinY
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
May 15, 2020, 02:39 PM
AMC2
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
May 15, 2020, 02:56 PM
MartinY
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
May 15, 2020, 03:39 PM
AMC2
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
May 15, 2020, 04:26 PM
AMC2
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
May 15, 2020, 06:36 PM
AMC2
Am I missing something here?


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
May 15, 2020, 06:51 PM
FP Mod Chuck
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
May 17, 2020, 03:19 PM
AMC2
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