I want to perform a script activity on an Account Send Summary report I dropped into a Data Extension. The current numbers inside of the system cannot be logically filtered because they contain "," and "%". Is there a way to remove them and convert the string into numbers using ssjs in a script activity for automation?

I would prefer using ampscript but I can't use it inside of the script activity. If you know another way of me converting the numbers and filtering only the rows meeting my requirements, that would be helpful.

My requirements is to show results in an email where the campaign had less than 95% deliverable rate.

  • 1
    Can it be inside a SQL Query instead or does it have to be a script activity? Also as a note you can utilize AMPscript in a script activity, you just need to house it inside a content block - salesforce.stackexchange.com/questions/241809/… Nov 20 '19 at 21:53
  • 1
    I could put it inside of a SQL. I thought it would be more work to do it that way than using a script activity. But thinking about it, I could use the SQL to convert and filter the data. I'll try that instead.
    – HHF21
    Nov 21 '19 at 14:04
  • Here is a sample that I used to remove the comma and set to a number: CONVERT(NUMERIC(10,0), REPLACE(r.Bounces, ',','') ). Could probably do the same to percent ones too. Just need to make sure to change numeric to include decimals as well. Nov 21 '19 at 16:08

I would highly recommend that you take care of this via SQL instead of AMPscript or SSJS as it would be much quicker and more efficient.

Below are 2 sample snippets to help you to remove the commas and percentages and turn them into numbers.

CONVERT(NUMERIC(10,0), REPLACE(r.Bounces, ',','') )

Input: 2,945
Output: 2945

CONVERT(NUMERIC(10,2), REPLACE(r.BounceRate, '%','') )

Input: 24.95%
Output: 24.95

Please note with the percent that it will be changed to a decimal and will not include the percent sign. I would not use any formatting to turn this back into a percent, as it would take 24.95 and make it 2495% on output. I would recommend doing a concat and adding a % at the end, whether via SQL, AMPscript or SSJS when sending to output in the email/CP.

  • 1
    Thanks. I queried the data using convert and replace and it worked perfectly. All my numbers were came through.
    – HHF21
    Nov 21 '19 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.