I need to know how many of 6 specific columns (Col1, Col2, Col3, Col4, Col5, Col6) from a lookup Data Extension are empty. If there are less or equal to 3 empty I need to display something, else will display something else.

What's the easiest way to do that with AMPScript? Thanks.


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"
      set @content = "something else"


    <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 ]%%


col1: col1val 
col5: col5val 

emptycount: 4 

content: something else 


AMPScript Lookup Examples

  • Adam, always to the rescue! Thanks mate, will try it out tomorrow. – Snowalker Feb 17 '16 at 22:08
  • hey Adam, this is what I have but it gives me "Unable to generate preview". Do you see anything wrong? I have "DM" in both subscribers list and the lookup file: codeshare.io/vDSDZ – Snowalker Feb 18 '16 at 14:19
  • what error does the toolbar validate show? – Adam Spriggs Feb 18 '16 at 15:22
  • no, the error is in ExactTarget, Adam. – Snowalker Feb 18 '16 at 15:32
  • OK. Fixed my example. left off the second iif() parameter. – Adam Spriggs Feb 18 '16 at 15:40

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.