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.

1 Answer 1


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
    Commented Feb 17, 2016 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
    Commented Feb 18, 2016 at 14:19
  • what error does the toolbar validate show? Commented Feb 18, 2016 at 15:22
  • no, the error is in ExactTarget, Adam.
    – Snowalker
    Commented Feb 18, 2016 at 15:32
  • OK. Fixed my example. left off the second iif() parameter. Commented Feb 18, 2016 at 15:40

You must log in to answer this question.

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