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
Improve this question

1 Answer 1

Reset to default
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
Improve this answer
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!
    – apples-oranges
    Commented Jun 8, 2021 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,'")]%', '%'))
    – Gortonington
    Commented Jun 8, 2021 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.
    – apples-oranges
    Commented Jun 8, 2021 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!
    – Gortonington
    Commented Jun 8, 2021 at 21:59
  • Thanks for all the help! :)
    – apples-oranges
    Commented Jun 8, 2021 at 22:58

You must log in to answer this question.

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