0

I am trying to concat all the non empty field values from a row in my DE, into a single variable (each value in the concatenated string separated by a comma).

The field names are: FIELD1, FIELD2 and FIELD3. Hence I tried adding a counter @i to the field name in a loop, but no luck.

For example, if only FIELD2 and FIELD3 had values( 'abc' and 'def') and FIELD1 was empty, my desired output would be: "abc,def"

set @Id = 'test.gmail.com'
set @rows = LookupRows("MyDE","Id", @Id)
set @rowCount = rowcount(@rows) 
set @row = row(@rows,1)   
set @strng = ""

/* here is the loop where I try to concat the non empty values */

FOR @i = 1 TO 3 DO
  if not empty(field(@row,CONCAT("FIELD", @i))) then
      set @strng = field(@row,CONCAT("FIELD", @i, ','))
  else
  endif
NEXT @i 
2

Slight change should get you there:

Basic Solution:

set @Id = 'test.gmail.com'
set @rows = LookupRows("MyDE","Id", @Id)
set @rowCount = rowcount(@rows) 
set @row = row(@rows,1)   

SET @Field1 = FIELD(@row,"Field1")
SET @Field2 = FIELD(@row,"Field2")
SET @Field3 = FIELD(@row,"Field3")

SET @strng = CONCAT(IIF(NOT EMPTY(@Field1), CONCAT(@Field1,","), ""), IIF(NOT EMPTY(@Field2), CONCAT(@Field2,","), ""), IIF(NOT EMPTY(@Field3), @Field3, ""))
]%%

Which if

Field 1 = '' 
Field 2 = 'not empty'
Field 3 = 'I have content'

Then @strng = not empty,I have content

Now if you have a high number of fields and require a for loop instead of manually writing each out, you could try:

set @Id = 'test.gmail.com'
set @rows = LookupRows("MyDE","Id", @Id)
set @rowCount = rowcount(@rows) 
set @row = row(@rows,1)   
set @strng = ""

FOR @i = 1 TO 3 DO
  set @tempTAC = TreatAsContent(CONCAT('%','%[ SET @tempVal = ', 'Field(@row, "Field',@i, '")', ']%', '%'))
  if not empty(@tempVal) then
      set @strng = IIF(EMPTY(@strng), @tempVal, CONCAT(@strng,',',@tempVal))
  endif
NEXT @i 
5
  • Thanks! I tried to expand it by for each iteration to do a lookup with @tempVal in another DE. And then concat that value to @string. Seems to work fine, except for the first iteration. Then it does not give me a value for the lookup.... This is my code: set @tempTAC2= TreatAsContent(CONCAT('%','%[ SET @tempVal2 = ', 'Lookup("SecondDE","Title","Country", @tempVal)' , ']%', '%')). Then I add the var here: set @strng = IIF(EMPTY(@strng), @tempVal, CONCAT(@strng,',',@tempVal, @tempVal2 )). Any idea what is going wrong here :) Thanks! Jun 8 at 16:13
  • 1
    I bet its because in the CONCAT you need to have the @tempVal not be part of the 'string' but instead declared as the var and wrap it in double quotes ("). Like so: set @tempTAC2= TreatAsContent(CONCAT('%','%[ SET @tempVal2 = Lookup("SecondDE","Title","Country","', @tempVal,'")]%', '%')) Jun 8 at 16:41
  • 1
    Thank again! But no luck Im afraid. What seems to do the trick is the following (still using my script). In the line where we create the 'string' I add @tempVal2 in the IF statement: set @strng = IIF(EMPTY(@strng), CONCAT(@tempVal, @tempVal2), CONCAT(@strng, ',' ,@tempVal, @tempVal2)). So, when 'string' is empty then do CONCAT(@tempVal, @tempVal2), rather than only @tempVal. Jun 8 at 21:48
  • 1
    You are right, would need to be added to both. I was completely concentrating on the wrong part of your issue - thought @tempVal2 was returning blank. Great work figuring it out! Jun 8 at 21:59
  • Thanks for all the help! :) Jun 8 at 22:58

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.