2

I have 3 Fields showing 'number of rewards' available that are decimal inputs.

Is there a way I can display a sum of the 3 columns in a field in a data extension so that number can be used in email personalisation?

EG:

Field a - 2
Field b - 6
Field c - 1

Sum
Total - 8

Alternatively, is this possible with AMP script?

also, is it possible to combine with a Lookup if data is stored in a separate table and linked through a contact id?

EG - %%[set @contactId =[ContactId]]%% %%=Lookup('othertable','field1','ContactID',@contactId)=%%

Something like:

%%[set @contactId =[ContactId]]%% %%[set @field1 =[%%=Lookup('othertable','field1','ContactID',@contactId)=%%] %%[set @field2 =[%%=Lookup('othertable','field1','ContactID',@contactId)=%%] %%[ SET @total = Add(field1,field2) ]%%

disclaimer - this doesn't work, but an example of what I'm trying to pull off!

2 Answers 2

5

Yep, this is possible via ampscript and SQL. For SQL you would need to run a query as a update. This should work:

SELECT (field1+field2+field3) AS TotalSum 
FROM TableName

Ampscript solution using the Add function (assuming field1 and field 2 are in sendable DE)

%%[
SET @total = Add(field1,field2)
SET @total = Add(@total,field3)
]%%

Rounding to the nearest whole number can be achieved using the FormatNumber function in Ampscript

%%=FormatNumber(@total,"N")=%%
7
  • Excellent, thanks! Can you think of a way this can combined with a Lookup in the case data is updated separately to contact data?
    – Ben Owen
    Oct 31, 2019 at 10:50
  • Have added info in the original question
    – Ben Owen
    Oct 31, 2019 at 10:59
  • as long as you have a a value to look up against in your other table i dont see why a lookup followed by ampscript addition wont work, you should probably use contact key/subscriber key rather than contactID which is a system identifier. When you say it doesn't work what do you mean? are you getting a error. Are you getting results returned from your lookup?
    – Noor Alam
    Nov 17, 2019 at 22:42
  • Hi Noor, yes when trying the snippet of AMP I added as an edit to the original question I got an error message when attempting to preview a test email, I'll revisit again with a fresh set of eyes but I went through every possible variant of field names I could to ensure it wasn't a simple syntax error.
    – Ben Owen
    Nov 19, 2019 at 10:23
  • help.salesforce.com/… contactID is not there as a valid personalisation string, as mentioned previously I'd recommend subscriberKey
    – Noor Alam
    Nov 19, 2019 at 12:06
0

You can use the Add function in AMPscript and Subtract function.

    %%[ 
         set @Avalue = Subtract(@a,2) 
         set @Bvalue = Subtract(@b,6)
         set @Cvalue = Subtract(@c,1)
         set @sum = Add(@Avalue, @Bvalue)
         set @sum2 = Add(@sum, @Cvalue)
    ]%%
1
  • Add only takes 2 params
    – Noor Alam
    Apr 10, 2019 at 13:18

You must log in to answer this question.

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