I have an alpha field(A40V) with the values such as (12.50%,2,89%,5.00% etc) and I want to change it to a numeric field.
Iam using the below code/logic but nothing seems to be working :
DEFINE FILE NEW_TABLE
NEW_FIELD2/D12.2 = ATODBL(FIELD2,'40',NEW_FIELD2);
END
TABLE FILE NEW_TABLE
PRINT
FIELD2
COMPUTE NEWER_FIELD/P12.2=NEW_FIELD2;
END
-RUN
This is the output which Iam getting :
FIELD2 NEWER_FIELD
12.50% .00
2.89% .00
5.00% .00
....
Could anyone please let me know how to convert from alpha to numeric retaining the %'s ? Please let me know.
Thanks a lot in advance for all the help.
Regards, IPThis message has been edited. Last edited by: info4pal,
Look in documents for USAGE (same use for DEFINE and COMPUTE also) and learn how use percent sign on number.
Kofi
Client Server 8.1.05: Apache; Tomcat;Windows Server 2012 Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
March 14, 2017, 08:03 AM
MartinY
USAGE is good when your data is read from a master file. If it comes from a HOLD file or defined in your TABLE FILE, you may not have a master from which you can change the USAGE.
This can do the job. To have EDIT converting an "alpha-number" to a number, it must not have any spaces or special characters. Sometimes you need to play with several functions and try stupid things to get what you need.
DEFINE FILE CAR
PCTA /A10V = DECODE COUNTRY ('ENGLAND' '10.5%' 'FRANCE' '15.28%' 'ITALY' '-5.00%' 'JAPAN' '.05%' ELSE '100%');
PCTX /A10V = TRIMV('B', CTRAN (10, PCTA, 37, 32, 'A10V'), 10, '', 1, 'A10V');
PCTD /D10.2%C = EDIT(TRIMV('B', CTRAN (10, PCTA, 37, 32, 'A10V'), 10, '', 1, 'A10V'));
END
TABLE FILE CAR
PRINT PCTA
PCTX
PCTD
BY COUNTRY
END
-RUN
This message has been edited. Last edited by: MartinY,
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
The DECODE was there only to create data as for the sample since no data similar to yours exist in IBI sample files.
In your case PCTA is replaced by PIVOTFLD in the PCTD field definition.
You need to analyse a little more and think "what can be applied to my case from the provided sample".
From all the posts regarding that report/graph "issue", I gave you all what you need to have the result. I think that from this point, you have to think and work by yourself. Try stuff, do more search, make errors and try back. You have all you need already.This message has been edited. Last edited by: MartinY,
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
March 14, 2017, 09:03 AM
Kofi
quote:
PCTD /D10.2%C
Exactly!!! Learn number format by reading manual and document and not expect people do for you.
You lucky Martin so keen to answer and do your work!!
quote:
Look in documents for USAGE (same use for DEFINE and COMPUTE also) and learn how use percent sign on number.
Martin, USAGE OK on synonym as you say. Is why I say same use for DEFINE and COMPUTE. I try give enough to help go forward but not complete answer - this way IP may learn??
Kofi
Client Server 8.1.05: Apache; Tomcat;Windows Server 2012 Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
March 14, 2017, 09:09 AM
MartinY
It's right Kofi, but using format attributes may not be enough since it may not converting from one type to another. Often it's just a display feature attribute.
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
March 14, 2017, 09:19 AM
info4pal
Hi,
Thanks Kofi
Martin - Thanks a million for all your help and you are a rockstar Your knowledge is vast and incredible!!