[SalesForce] sum the total value of 3 columns in a data extension

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!

Best Answer

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")=%%

Related Solutions

[SalesForce] AMPScript: Count non-empty columns in lookup Data Extension

Something like this should do the trick:

%%[
var @rows, @row, @rowCount
var @lookupValue
set @lookupValue = "whee"

set @rows = LookupRows("DataExtensionName","LookupColumn", @lookupValue)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    var @col1, @col2, @col3, @col4, @col5, @col6

    set @row = row(@rows,1)
    set @col1 = field(@row,"col1")
    set @col2 = field(@row,"col2")
    set @col3 = field(@row,"col3")
    set @col4 = field(@row,"col4")
    set @col5 = field(@row,"col5")
    set @col6 = field(@row,"col6")

    var @emptycount, @content

    set @emptyCount = 0

    set @emptyCount = iif(empty(@col1),add(@emptyCount,1),add(@emptyCount,0))
    set @emptyCount = iif(empty(@col2),add(@emptyCount,1),add(@emptyCount,0))
    set @emptyCount = iif(empty(@col3),add(@emptyCount,1),add(@emptyCount,0))
    set @emptyCount = iif(empty(@col4),add(@emptyCount,1),add(@emptyCount,0))
    set @emptyCount = iif(empty(@col5),add(@emptyCount,1),add(@emptyCount,0))
    set @emptyCount = iif(empty(@col6),add(@emptyCount,1),add(@emptyCount,0))

    if @emptyCount <= 3 then
      set @content = "something"
    else
      set @content = "something else"
    endif

]%%

    <br>col1: %%=v(@col1)=%%
    <br>col2: %%=v(@col2)=%%
    <br>col3: %%=v(@col3)=%%
    <br>col4: %%=v(@col4)=%%
    <br>col5: %%=v(@col5)=%%
    <br>col6: %%=v(@col6)=%%
    <br><br>emptycount: %%=v(@emptycount)=%%
    <br><br>content: %%=v(@content)=%%

%%[ else ]%%

No rows found

%%[ endif ]%%

Output

col1: col1val 
col2: 
col3: 
col4: 
col5: col5val 
col6: 

emptycount: 4 

content: something else

Reference:

AMPScript Lookup Examples

[SalesForce] can i use lookup to display data extension column value

The LookUpRows function is returning a rowset. You should use LookUp for this use case.

You would also have to use IF control for cases where it doesn't match i.e Dear Customer

Example:

%%[

var @pullname , @nullpullname /* It is best practice is always declare your variables */

SET @nullpullname = 'Dear Customer' /* This is just an example to help you see how / why you should add flow control to this */
SET @pullname = Lookup("DataExtensionName","NameColumn", "EmailAdd_Column", emailaddr)

]%%

%%[IF @pullname IS NOT EMPTY THEN]%%

%%=v(@pullname)=%%

%%[ELSE]%%

%%=v(@nullpullname)=%%


%%[ENDIF]%%

Best Answer

Related Solutions

[SalesForce] AMPScript: Count non-empty columns in lookup Data Extension

[SalesForce] can i use lookup to display data extension column value

Related Topic