0

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.

Improve this question
3
  • 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/…
    – Gortonington
    Commented Nov 20, 2019 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
    Commented Nov 21, 2019 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.
    – Gortonington
    Commented Nov 21, 2019 at 16:08

1 Answer 1

Reset to default
1

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.

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

Input: 2,945
Output: 2945

Percent:
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.

Improve this answer
1
  • 1
    Thanks. I queried the data using convert and replace and it worked perfectly. All my numbers were came through.
    – HHF21
    Commented Nov 21, 2019 at 16:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .