Validating amount value.

[Former Member]

Hi,

I am uploading data into a custom table in that one field is there with amount. Let us say if user have entered some wrong amount i mean any additional characters like 12!45.78 in the excel file (In this case ! symbol is a special character which needs to be deleted) and write into my custom table as 1245.78. Any hints.

Regards

VEnk@

[Former Member]

hello,

if you are using FM 'GUI_UPLOAD' for uploading, pass value 'X" to the parameter " DAT_MODE". it can upload the numeric valuecorrectly.

this will work only if you use GUI_UPLOAD FM

regards ,

Prem

[Former Member]

I am using FM ALSM_EXCEL_TO_INTERNAL_TABLE to upload my file.

[Former Member]

Hello Venkat,

Check if the FM SF_SPECIALCHAR_DELETE helps after uploading the excel.

Vikranth

[Former Member]

Hi Vikranth,

Using FM SF_SPECIALCHAR_DELETE will also delete the decima separator '.'.

Hi Venkat,

My following idea also comes from Vikranth's suggestion - that is the way FM SF_SPECIALCHAR_DELETE does:

constants: con_specharsear(50) value
     ''' _ < > ! " & / = ? : ; , - ( ) # # % ^ $ | ~ @ '.

data: lt_excel  type standard table of alsmex_tabline.
data: lw_excel like line of lt_excel.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  exporting
    filename                = im_file_path
    i_begin_col             = 1
    i_begin_row             = 1
    i_end_col               = 15
    i_end_row               = 65000
  tables
    intern                  = lt_excel
  exceptions
    inconsistent_parameters = 1
    upload_ole              = 2
    others                  = 3.

if sy-subrc = 0.
* Populate data to internal table
  sort lt_excel by row col.

  loop at lt_excel into lw_excel.
*   Check if field is amount field
    if field is amount.
      translate lt_excel-value using con_specharsear.
      condense lt_excel-value no-gaps. " Delete predefined chars
    endif.
    
*   Then move the value to your internal table
    it-amount = lt_excel-value.
    ....
  endloop.
endif.

[SuhaSaha]

I will go for the solution proposed by Khanh, but prior to this you have to define a list with all the special characters.

Anyways i always prefer the users to restrain some caution while preparing the data. We are not here to clean up their mess

BR,

Suhas

[Former Member]

Hi,

Since the numeric values can contain a ',' '.' or a space for a decimal separator, these values are maintained in table USR01-DCPFM.

Before correcting/eradicating the entry which contains an invalid character, do a check on which is the decimal seprator.

{set for User's Login in system} by using a select query-

SELECT * FROM USR01 WHERE BNAME = SY-UNAME

The field DCPFM will contain the correct decimal separator.

Based on what is maintained, you can create a constant that contains all non-numeric charatcers + decimal separatir and do a

logical operation 'CO'/'NA' on your field amount.

-- Dedeepya C

Edited by: dedeepya reddy on Apr 21, 2010 9:59 AM

[Former Member]

Thank you very much to all you guys i have implemented the ideas given, but my last query is if the amount is 313.75.21 in this case two points are there which will again take this to dump. So any hints how to over come from 2 or more points.

Regards

VEnk@

[Former Member]

Can u bee bit more clear plz.

[Former Member]

There are 3 formats maintained in any sap system.

1) 1.234.567,89 = here the '.' is the thousands separator and ',' is the decimal separator {DCPFM = ' ' }

2) 1,234,567.89 = here the ',' is the thousands separator and '.' is the decimal separator {DCPFM = X }

3) 1 234 567,89 = here 'space' is the thousands separator and ',' decimal separator {DCPFM = Y }

U can check what format/setting is maintained for your profile by checking the entry in USR01 table passing

your login id under BNAME field.

From the example u've cited '313.75.21' has '.' as thousands separator and not a decimal separator right, it looks like ' ' is the DCPFM set in ur case. In such a case both '.' and a ',' hold valid as a part of amount value.

Just tat u ve to ensure that ',' if there in amount must be the last separator i.e there should be no '.' following ','.

This u can implemet by applyin a logical snippet.

Hope i was clearer here.

-- Dedeepya

[Former Member]

Hi VEnk@,

I personally second Suha's cautioning words on cleaning up faulty data. It can be very misleading to automatically fix the data, because this means that you're assuming you know the intent of the user (which might backfire in some situations). So from an application design I think it would be much cleaner to indicate the faulty rows/cells to the user.

A good design might be to split your data into good data, which can be loaded and into another set of bad data, that will not be loaded but somehow logged so that the user can later correct it. If you've ever used an extract without inspecting the data and loaded it into a forgiving application that tries to load anything instead of telling you that something is wrong, then you know how annoying that is (because you discover the problem probably only much later down the road once queries go wrong).

Anyhow, assuming that all this is not deterring you and you want to go ahead with your solution, here are two very simple ABAP statements that weed out all non-digits in the string VALUE:

replace all occurrences of regex '[^0-9.]' in VALUE with ''.
replace all occurrences of regex '\.(?=.*\.)' in VALUE with ''.

It basically first removes all non-numeric characters (except for decimal point) and then kills all but the last decimal point (to answer your last question).

What's wrong with this approach? Should you have numbers in scientific notation (e.g. '1.4E3') you'd basically return a completely wrong number. Any negative number will be quietly turned into a positive number (and I don't see how you could come up with an easy way to figure out what '14-3' should mean). As pointed out by Dedeepya the decimal point setting depends on the locale; however, here we should be more precise and say it depends on the locale of the person who created the file, not actually the locale configured for the person uploading the file in SAP (so unless there's a strong connection because it's the same person, I don't see why using this information might be of any value).

So please do us all a favor and don't do this kind of data cleansing that most likely will give support people a major headache later...

Cheers, harald

[Former Member]

Hi Venkat,

I agree with everyone here is that we need the user to key in correct data.

But I still support you an idea to do your requirement.

That is you can write a subroutine with the idea as following:

data: lv_decimal type string.

lv_decimal = '123.123.123'.

perform convert_decimal changing lv_decimal.

write: lv_decimal.


*&---------------------------------------------------------------------*
*&      Form  convert_decimal
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_DECIMAL  text
*----------------------------------------------------------------------*
form convert_decimal changing p_decimal.
  data: lt_string type table of string.
  data: lv_string type string.
  data: lv_line type i.

  split p_decimal at '.' into table lt_string.

  lv_line = lines( lt_string ).

  if lv_line > 2.
    clear: p_decimal.
    loop at lt_string into lv_string.
      if sy-tabix < lv_line.
        concatenate p_decimal lv_string into p_decimal.
      else.
        concatenate p_decimal lv_string into p_decimal separated by '.'.
      endif.
    endloop.
  endif.

endform.                    "convert_decimal

Regards,

Khanh

Edited by: Khanh Nguyen on Apr 21, 2010 6:00 PM

[SuhaSaha]

What if the decimal separator is ',' & not '.' ?

[Former Member]

Hello Susha,

We can define a decimal separator variable:

data: lv_dec_sept(1) type c.

1. select DCPFM from USR01 with current user login.

Then base on the value of USR01-DCPFM we can set the decimal separator:

If value = '' or 'Y' --> lv_dec_sept = ','.

else --> lv_dec_sept = '.'.

2. Then we process the same with the separator instead of hard coding.

That's my idea.

Regards,

Khanh

[Former Member]

We've already explained that factor -- decimal separator being '.' or a ',' or a plain space.

Those 3 are the only extra special characters i can say that might appear in a amount value which if declared is of type I or P.

VEn@ can just add a bit of coding to remove the respective character from his constant

[Former Member]

We've already explained that factor -- decimal separator being '.' or a ',' or a plain space.

Those 3 are the only extra special characters i can say that might appear in a amount value which if declared is of type I or P.

VEn@ can just add a bit of coding to remove the respective character from his non-numeric constant holder and adjust his amount field accordingly.

@ VEnk@ have you had any progress on this issue with such a many inputs?

Cheers

Dedeepya

[Former Member]

Hi All,

Thanks alot for all your help it was really helpful. Check my code even..

lw_string = 'Some value populated'

REPLACE ALL OCCURRENCES OF ',' IN lw_string WITH space.

CONDENSE lw_string NO-GAPS.

IF lw_string CO '0123456789+-. '.

SPLIT lw_string AT '.' INTO lw_var1 lw_var2.

REPLACE ALL OCCURRENCES OF '.' IN lw_var2 WITH space.

IF sy-subrc = 0. "IF sy-subrc fails then the amt is correct else triggers a msg.

CONCATENATE lw_msg

lw_string

INTO lw_amtmsg SEPARATED BY space.

APPEND lw_amtmsg TO it_ezcfmee.

w_flag = 'X'.

CLEAR lw_amtmsg.

ELSE.

fs_zcfmee-ueamt = lw_string.

ENDIF.

ELSE.

CONCATENATE lw_msg

lw_string

INTO lw_amtmsg SEPARATED BY space.

APPEND lw_amtmsg TO it_ezcfmee.

w_flag = 'X'.

ENDIF.

Regards

VEnk@

[Former Member]

DATA text TYPE string VALUE '123!.123.123'.

REPLACE ALL OCCURRENCES OF REGEX '[^0-9.]' IN text WITH ''.
REPLACE ALL OCCURRENCES OF REGEX '\.(?=.*\.)' IN text WITH ''.

WRITE TEXT.

OUTPUT = 123123.123

[Former Member]

closed

[Former Member]

closed