For loop to display data only when DE has data in it

Question

There are few fields in the Data Extension DeptID, BusinessID, StateID. I want it to print the value only if that value is present in the DE.

Eg, if the DeptID is blank/null it should print BusinessID and StateID(If they are present in the DE) or else a message like "Invalid". But if the DeptID is not present it should not display comma(,) like 456, 789 where 456 is the BusinessID and 789 is the StateID

If all 3 are present then it should print something like 123,456,789. I am totally new to AMPScript and not sure how to lookup and achieve this? Can I use for loop, if yes how?

Answer 1

This is all possible using AMPscript. You will have to build the following script:

1. Use the LookupRows function to find the rows you would like to display for each subscriber: https://ampscript.guide/lookuprows/

2. Use a if/else statement to check if any rows were found for a particular subscriber: https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/languageElements.htm

3. Build a for loop: http://sfmarketing.cloud/2019/11/29/loops-in-ampscript-and-server-side-javascript/

4. Use an additional if/else statement to fulfill the requirement for displaying data from different columns you described

Here’s an example which does more or less what you are looking for, without the if/else statements:

%%[ var @rows, @row, @rowCount, @subKey, @counter, @orderItem 

set @subKey = _subscriberkey 
set @rows = LookupRows("Orders","CustomerId", @subKey) 
set @rowCount = rowcount(@rows) 

for @counter = 1 to @rowCount do /* repeat in a loop for as many times as defined in rowCount */ 
      set @row = row(@rows, @counter) /* get row based on counter */ 
      set @orderItem = field(@row,"OrderItem") 

]%%

<br>
Order item %%=v(@counter)=%%: %%=v(@orderItem)=%%<br>

%%[ next ]%% 

Answer 2

Fre deciding what the output is you can use an IF statement. If you have multiple entries then you would want to wrap this with a Loop.

    %%[
set @DeptID = [DeptID]
set @BusinessID = [BusinessID]
set @StateID = [StateID]

if empty(@DeptID) and empty(@StateID) and empty(@BusinessID) then 
     set @output = "Invalid"
elseif  empty(@DeptID) and empty(@StateID) and not empty(@BusinessID) then
     set @output = @BusinessID
elseif empty(@DeptID) and not empty(@StateID) and empty(@BusinessID) then
     set @output = @StateID
elseif  empty(@DeptID) and not empty(@StateID) and not empty(@BusinessID) then
     set @output = Concat(@StateID,",",@BusinessID)
elseif not empty(@DeptID) and empty(@StateID) and empty(@BusinessID) then
     set @output = @DeptID
elseif not empty(@DeptID) and empty(@StateID) and not empty(@BusinessID) then
     set @output = concat(@DeptID,", "@BusinessID)
elseif not empty(@DeptID) and not empty(@StateID) and empty(@BusinessID) then
     set @output = concat(@DeptID,","@StateID)
elseif not empty(@DeptID) and not empty(@StateID) and not empty(@BusinessID) then
     set @output = Concat(@DeptID,","@StateID,",",@BusinessID)
else then
     set @output = "Error"
endif
 ]%%